Why can't I handle the same error more than once?

Question

In this subroutine below, I am intentionally causing an error by dividing by 0. The 1st time this error occurs, it jumps to the handler called 'errHandler', the 2nd time the error occurs however, the execution stops and 'Division by 0' error is raised.

How can I make sure that for each error, it does jump to 'errHandler'?

(this is the core problem of something I'm going with my SQL + VBA program I'm writing)

Sub errorTest()

Dim i As Long

For i = 0 To 2
    On Error GoTo errHandler
    Debug.Print 8 / 0
continue:
Next i

Exit Sub

errHandler:
Err.Clear
On Error GoTo 0
GoTo continue
    
End Sub

https://stackoverflow.com/questions/1038006/what-are-some-good-patterns-for-vba-error-handling — braX, Dec 26 '21 at 23:18

score 1 · Accepted Answer · answered Dec 26 '21 at 23:35

1

Handling Errors in a Loop

Next means that it will continue with the statement right after the statement the error occurred in (Next i).

Sub errorTest()

    Dim i As Long
    
    For i = 0 To 2
        On Error GoTo errHandler
        Debug.Print 8 / 0
    Next i
    
    Exit Sub
    
errHandler:
    Debug.Print i, "Run-time error '" & Err.Number & "': " & Err.Description
    Resume Next ' Resets the error, but keeps the routine 'active'.
End Sub

answered Dec 26 '21 at 23:35

VBasic2008

44,888
5
17
28

This is perfect! The closest thing I was trying before was 'Resume', but not 'Resume Next'. Thanks for the help! – Finch Dec 26 '21 at 23:42
`Resume` tries the same statement i.e. in this case, creating an endless loop (crashing Excel). – VBasic2008 Dec 26 '21 at 23:43

Why can't I handle the same error more than once?

1 Answers1

Handling Errors in a Loop