Handling Recurring VBA Errors Within Subroutine

Question

I am stuck on something that I have a workaround for, but it bugs me I don't have a direct answer for how to address the issue of using On Error Goto for recurring errors. My question is essentially the same as this one, however the answers provided are alternatives to the OP's overall approach rather than how to handle the specific issue.

I've simplified an example below. I am WELL AWARE that there are probably a dozen ways this code could be rewritten to avoid any error -- I am just using this to illustrate the issue of trying to figure out why/how does this code work properly when i = 0, but not when i = 3,6,9?

Sub vbaErrorConfusion()
Dim theArray(9) As Long, i As Long

    For i = 0 To 9
        On Error GoTo fixingErrors
        'next line will intentionally create an error when
        'when i = {0} this works as expected, but at i=3 next line throws an error.
        theArray(i) = i + 1 / (i Mod 3)
        On Error GoTo 0
    
next_i:
    Next i

Exit Sub
'----Error Handling----
'this works as expected when i=0 but not when i = 3,6,9
fixingErrors:
    
    Err.Clear
    On Error GoTo 0
    'at this point I would expect my error state to be the same as the start of procedure?
    theArray(i) = -1
    GoTo next_i
End Sub

What my Err variable shows after first instance of 0.

After I run Err.Clear I would expect the behavior for i=3 to be the same as when i=0, however my procedure stops with the below VBA error one would expect WITHOUT any error catching.

I presume there's some way to reset the Error variable or handle this type of situation without a workaround? Any quality responses will get upvoted. Thanks.

The answer from FreeFlow below is correct. Using Err.Clear only clears the text and numbers from the Err object it doesn't clear the error. See [this](https://excelmacromastery.com/vba-error-handling/#Using_ErrClear). It is a bit counter intuitive. — Paul Kelly, Dec 02 '20 at 02:13
Thanks @PaulKelly (aka *King of Error Trapping* as explained in his resourceful page here: https://excelmacromastery.com/vba-error-handling) — pgSystemTester, Dec 20 '20 at 03:03

score 1 · Answer 1 · answered Nov 29 '20 at 18:36

Please take some time to read up on how Error handling in VBA works.

Sub vbaErrorConfusion()
    Dim theArray(9) As Long, i As Long

    For i = 0 To 9
        On Error GoTo fixingErrors
        'next line will intentionally create an error when
        'when i = {0} this works as expected, but at i=3 next line throws an error.
        theArray(i) = i + 1 / (i Mod 3)
        'On Error GoTo 0
    
'next_i:
    Next i

    Exit Sub
    '----Error Handling----
    'this works as expected when i=0 but not when i = 3,6,9
fixingErrors:
    
    Err.Clear
    Debug.Print "An error was generated for i= ", i
    'On Error GoTo 0
    'at this point I would expect my error state to be the same as the start of procedure?
    theArray(i) = -1
    Resume Next
End Sub

Thanks, your code does work but I liked the explanation provided from the other answer. I upvoted your answer here and I checked out a couple others of yours and gave this post a click as I thought it was useful (https://stackoverflow.com/questions/62994772/remove-duplicates-in-vba-combobox/62996373#62996373). Thanks — pgSystemTester, Dec 20 '20 at 02:59

score 1 · Accepted Answer · answered Dec 02 '20 at 09:43

To tell VBA that an you have dealt with the error you need a Resume statement. Therefore, replacing the line GoTo next_i with Resume next_i you give you the outcome you expect.

You do not need Err.Clear. Also, On Error GoTo 0 will disable the error handler. However, neither of these lines will tell VBA that you have dealt with an error.

In your code at i=0 the error handler is activated but VBA is not told that the error has been dealt with (i.e. no Resume statement). At i=3 another error occurs while a previous error hadn't been dealt with. In this case the current procedure/function/property cannot deal with the second error, which is therefore, fatal.

You should also take the On Error GoTo fixingErrors line outside the loop.

Sub vbaErrorConfusion()
On Error GoTo fixingErrors
Dim theArray(9) As Long, i As Long

    For i = 0 To 9
        '*On Error GoTo fixingErrors
        'next line will intentionally create an error when
        'when i = {0} this works as expected, but at i=3 next line throws an error.
        theArray(i) = i + 1 / (i Mod 3)
        '*On Error GoTo 0
    
next_i:
    Next i

Exit Sub
'----Error Handling----
'this works as expected when i=0 but not when i = 3,6,9
fixingErrors:
    
    '*Err.Clear
    '*On Error GoTo 0
    'at this point I would expect my error state to be the same as the start of procedure?
    theArray(i) = -1
    Resume next_i
End Sub

Handling Recurring VBA Errors Within Subroutine

2 Answers2

Linked