VBA Excel Overflow -- no problems when I step into, but Overflow error 6 when I run the test regularly. ChatGPT has its limits
When I run the code I get a runtime error 6: Overflow. When I step into, everything is as expected. Named ranges exist, pretty sure I've handled the number (decimal) sizes. Any ideas? I've tried other versions with intermediate variables, but shouldn't be necessary since none of the values in the exponent are ever very big. This is a Makeham's Law survival function for actuarial purposes, and all the values are consistent with normal lifetime parameters
Function SurvivalF(age As Double, t As Double) As Double
Debug.Print ">>> SF entered"
Dim a As Double, b As Double, c As Double
a = GetParam("_a_")
b = GetParam("_b_")
c = GetParam("_c_")
Debug.Print ">>> Parameters loaded: a = "; a; ", b = "; b; ", c = "; c
Dim exp_b_age As Double, exp_b_t As Double
On Error GoTo OverflowHandler
exp_b_age = Exp(b * age)
exp_b_t = Exp(b * t)
Dim hazard As Double
hazard = (a / b) * exp_b_age * (exp_b_t - 1) + c * t
SurvivalF = Round(Exp(-hazard), 7)
Debug.Print ">>> SF exited"
Exit Function
OverflowHandler:
Debug.Print "? Overflow in SurvivalF computation"
MsgBox "Overflow error: Exponential term too large. Check parameter 'b' or input age/t.", vbCritical
SurvivalF = 0
End Function
Sub Testing()
Debug.Print "------ New Testing Run at " & Now & " ------"
Debug.Print "Calling SurvivalF..."
Dim surv As Double
surv = SurvivalF(20, 2)
Debug.Print "Returned from SurvivalF"
Debug.Print "Result = " & surv
End Sub
'HelperFunctions
Function GetParam(name As String) As Double
On Error GoTo notFound
Dim rng As Range
Debug.Print "getting param for: "; name
Set rng = ThisWorkbook.Names(name).RefersToRange
If rng.Cells.Count <> 1 Then
Debug.Print "? GetParam ERROR: " & name & " refers to multiple cells"
MsgBox "Named range '" & name & "' must refer to exactly one cell.", vbCritical
GetParam = 0
Exit Function
End If
If Not IsNumeric(rng.Value) Then
Debug.Print "? GetParam ERROR: " & name & " is not numeric"
MsgBox "Named range '" & name & "' must contain a numeric value.", vbCritical
GetParam = 0
Exit Function
End If
GetParam = CDbl(rng.Value)
Debug.Print "param " & name & " loaded: " & GetParam
Exit Function
notFound:
Debug.Print "? GetParam ERROR: named range '" & name & "' not found or invalid"
MsgBox "Named range '" & name & "' not found or invalid.", vbCritical
GetParam = 0
End Function
Answer
You're (probably) not actually getting a Runtime Error 6: Overflow. The error handler in your code is assuming that if an error happens, it must be because of an overflow error. This:
Debug.Print "? Overflow in SurvivalF computation"
MsgBox "Overflow error: Exponential term too large. Check parameter 'b' or input age/t.", vbCritical
SurvivalF = 0
...tells your code that when an error occurs, it should print "Overflow in SurvivalF computation" and display a message box with the "Overflow error:..." text, no matter what caused the error. Even if the error is that, say, a text string is being passed where an integer should be - a type mismatch error - your error handler is still going to report it as an overflow error.
First option would be to tell the code to break on all errors. This basically bypasses error handling, forcing the code to stop as soon as an error is encountered before even going to the error handler. In the VB Editor go to Tools | Options | General tab and select the option for "Break on All Errors".
Second option would be to remove your error handler altogether. Comment out the On Error Goto... line and the four lines of the error handler.
Third option would be to replace your error handler with something more informative. At the very least include a line like this:
Debug.print Error
That prints the actual error in the Immediate window.