Can't calculate long recursion

[alex009988]

Hello I'm trying to implement numerical methods in Excel. The first step is to create equation function and it seems work.
But when m is more than 170 I get the error type mismatch

Function PoissonEqs(p As Double, m As Integer)
If m = 0 Then
PoissonEqs = Exp(-p)
Else
PoissonEqs = Exp(m * Log(p) - p - Log(Application.Gamma(m + 1))) + PoissonEqs(p, m - 1)
End If
End Function
Sub test()
Dim a As Double
Debug.Print PoissonEqs(193.063, 170)
End Sub

For m 171 p is 194.125.
I need to get m as 1000 as the least.
Regards,
Alex

[Paul_Hossler]

Try making 'm' a Long instead of an Integer

[alex009988]

Thank you for your reply. I've tried m as long and m as double. Unfortunatelly, the same error appears to me.

[Paul_Hossler]

It looks like you're exceeding the capacity of a Double when you hit 172

Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from:

• -1.79769313486231E308 to -4.94065645841247E-324 for negative values
• 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Capture.JPG

[alex009988]

Thank you for your reply. I have another question.
In secand numerical Method I have on denominator something like this

Sub test2()
Dim a As Double
a = (Exp(-200) - 0.005) - (Exp(-183) - 0.005)
Debug.Print a
End Sub

The problem is that I have to work with f(x1)=0,005->f(x)=f(x1)-0.005=0 so it's rounded and with the result of that devision by zero got appeared.
Is there a way to keep off that problem? Thanks in Advance!

[Paul_Hossler]

No way I know since you're dealing with some very small floating point numbers

I have seen some high percision add on packs that you might investigate, but I have no experience with them

I believe that they use strings to emulate numbers and have their own add, subtract, etc. functions