Ignore overflow error when multiplication result is bigger than what a double can hold

During some iterative optimization, the following VBA code for the computation of the bivariate normal CDF sometimes throws an Overflow error on the line with z = hx * hy * c inside the while loop of the upper function.

I debugged the code and the overflow occurs when the numbers being multiplied result in a number bigger than what a double can hold.

Can you show me how to handle the problem by ignoring the iterations of the loop with such high values - I guess that's the only feasible solution (?). I tried myself with a On Error Goto nextiteration line before the multiplication and placing the nextiteration jump point before the Wend, but the error persists.

Function tetrachoric(x As Double, y As Double, rho As Double) As Double
    Const FACCURACY As Double = 0.0000000000001
    Const MinStopK As Integer = 20
    Dim k As Integer
    Dim c As Double
    Dim z As Double
    Dim s As Double
    Dim hx As Double
    Dim hx1 As Double
    Dim hx2 As Double
    Dim hy As Double
    Dim hy1 As Double
    Dim hy2 As Double
    Dim CheckPass As Integer

    hx = 1
    hy = 1
    hx1 = 0
    hy1 = 0
    k = 0

    c = rho
    z = c
    s = z
    CheckPass = 0

    While CheckPass < MinStopK
        k = k + 1
        hx2 = hx1
        hy2 = hy1
        hx1 = hx
        hy1 = hy
        hx = x * hx1 - (k - 1) * hx2
        hy = y * hy1 - (k - 1) * hy2
        c = c * rho / (k + 1)
        z = hx * hy * c
        s = s + z
        If Abs(z / s) < FACCURACY Then
            CheckPass = CheckPass + 1
        Else
            CheckPass = 0
        End If
    Wend
    tetrachoric = s
End Function


Public Function bivnor(x As Double, y As Double, rho As Double) As Double
'
' bivnor function
' Calculates bivariat normal CDF F(x,y,rho) for a pair of standard normal
' random variables with correlation RHO
'
If rho = 0 Then
    bivnor = Application.WorksheetFunction.NormSDist(x) * 开发者_如何学运维_
         Application.WorksheetFunction.NormSDist(y)
Else
    bivnor = Application.WorksheetFunction.NormSDist(x) * _
         Application.WorksheetFunction.NormSDist(y) + _
         Application.WorksheetFunction.NormDist(x, 0, 1, False) * _
         Application.WorksheetFunction.NormDist(y, 0, 1, False) * _
         tetrachoric(x, y, rho)
End If
End Function

Source: Available for download at http://michael.marginalq.com/

you're hitting on the limits of the computer architecture. Many complex algorithms can't be implemented 1:1 with their mathematical representation because of performance reasons and/or erroneous behavior when overflowing. There's an exceptionally good blog about these issues - John D. Cook.

Please take a look here for a better implementation.

You can also try binding an external library, that gives you arbitrary precision number handling, of course implemented using very expensive (in terms of CPU time) software algorithms. More can be found here.

Updated code using On Error Resume Next instead of On Error Goto:

While CheckPass < MinStopK
    k = k + 1
    hx2 = hx1
    hy2 = hy1
    hx1 = hx
    hy1 = hy
    hx = x * hx1 - (k - 1) * hx2
    hy = y * hy1 - (k - 1) * hy2
    c = c * rho / (k + 1)
    On Error Resume Next
    z = hx * hy * c
    If Err.Number = 0 Then
        s = s + z
        If Abs(z / s) < FACCURACY Then
            CheckPass = CheckPass + 1
        Else
            CheckPass = 0
        End If
    Else
        Err.Clear
    End If
Wend

http://www.codeproject.com/KB/recipes/float_point.aspx treats how to "Use Logarithms to Avoid Overflow and Underflow", which is a simple but quite effective way of working around overflow problems. In fact, it's so simple yet logical, why haven't we thought of that solution ourselves? ;)