VBA, Error Handling, Stack Overflow, Error Thrown After 2nd Loop Pass

Question

I am having an issue with actual Stack Overflow in my VBA Code.

I am trying to figure out VBA error handling.

I made a test sub to help figure this out.

I have an On Error GoTo Error_Handle

I want if Err.Number = 13 Start Over, not Resume Next.

If I try to loop GoTo Start_Over_Label:, on the second pass the error is still thrown.

If I try to loop Call the sub again, I receive a stack overflow error.

Is there is some way to loop in the Sub, without throwing an Error, or eating up the Call Stack?

Maybe there is a way to Resume Next, but start the Sub over?

I feel like there is a solution, but I am missing it.

Thank you in advance

Private My_Long As Long
Private My_Err_Counter As Long

Private Function My_Timer(My_Delay As Long)

    Dim My_Timer_Counter As Long
    While My_Timer_Counter <= My_Delay
        DoEvents
        My_Timer_Counter = My_Timer_Counter + 1
    Wend

End Function

Sub My_Error_Test()
My_Start_Over:
    On Error GoTo My_Error_Handle
    My_Timer (200)

    My_Long = "as"
    MsgBox My_Long

My_Error_Handle:
    Debug.Print "Err Num: " & Err.Number & " - " & Err.Description
    Debug.Print My_Err_Counter
    My_Err_Counter = My_Err_Counter + 1
    If Err.Number = 13 Then
        Err.Clear
        GoTo My_Start_Over
    End If
End Sub

doevents when used inappropriately often cause stack overflows by recursing into event handlers. The other thing is recusion itself, but it requires a lot of levels. The smallest stack you can count is 1 MB of storage. — user12431753

chris neilsen chris neilsen · Accepted Answer · 2020-02-17T02:17:45

The key issue is you should use Resume My_Start_Over rather than GoTo My_Start_Over. GoTo doesn't reset the error handling

Other issues you might like to address

Usually you'd have a Exit Sub befor the error handler. Without it the error handler code will execute when the Sub reaches that point.
Don't use () around parameters for a Sub (unless you want to override the ByRef parameters to ByVal
While / Wend is deprecated. Use Do While Loop instead
Local parameters are prefered
I've added some code so your Test will eventually complete

Private Function My_Timer(My_Delay As Long)
    Dim My_Timer_Counter As Long

    Do While My_Timer_Counter <= My_Delay
        DoEvents
        My_Timer_Counter = My_Timer_Counter + 1
    Loop
End Function

Sub My_Error_Test()
    Dim My_Long As Long
    Dim My_Err_Counter As Long
    Dim v As Variant
    v = "as"

My_Start_Over:

    On Error GoTo My_Error_Handle
    My_Timer 200
    If My_Err_Counter > 100 Then v = 1

    My_Long = v
    MsgBox My_Long
    My_Err_Counter = 0
Exit Sub
My_Error_Handle:
    Debug.Print "Err Num: " & Err.Number & " - " & Err.Description
    Debug.Print My_Err_Counter
    My_Err_Counter = My_Err_Counter + 1
    If Err.Number = 13 Then
        Err.Clear
        Resume My_Start_Over
    End If
End Sub

VBA, Error Handling, Stack Overflow, Error Thrown After 2nd Loop Pass

1 Answers