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

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

CodePudding user response：

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