How to go to next iteration on error AKA simulating Python's "continue" statement in VBA?

Question

In the following code, how do I make the loop continue on next iteraration - not next line - after the error is encountered?

Right now the code will not go past x=0:

Sub skip_iteration()

Dim x As Long

For x = 5 To -5 Step -1
    On Error GoTo errhandler:
    Debug.Print 15 / x
    Debug.Print "I don't want this to be printed on error"
Next x

errhandler:
If Err.Number = 11 Then
    Debug.Print ("Error on " & x)
End If

End Sub

I have looked into this answer: Skip to next iteration in loop vba among others, but could not translate it to my code.

Answer 1

You need Resume Next to go to the next line after the line, causing the error. And Err.Clear is a good practice as well.

The Exit Sub before the errhandler is also a good practice:

Sub SkipIteration()

    Dim x As Long        
    For x = 5 To -5 Step -1
        On Error GoTo errhandler:
        Debug.Print 15 / x
        Debug.Print "testing"
    Next x        
    Exit Sub

errhandler:
    If Err.Number = 11 Then
        Debug.Print ("Error on " & x)
        Err.Clear
        Resume Next
    End If    
End Sub

The difference between Resume and Resume Next is the following:

• Resume tries to go back to where it was called.

In the TestMeResume procedure, it throws Error 11 at the Debug.Print a/b and in the error handler it assigns b = 5. Then with Resume it retries the Debug.Print a / b, and as far as b is not 0, it runs.

• Resume Next goes to where it was called, ignores the line and continues with the next one.

In the TestMeResumeNext procedure, error 91 is thrown at a = 12 and the error handler is triggered. In the error handler a is assigned to Range("A14") and with Resume Next the a = 12 is skipped and the Sub continues.

---

Sub TestMeResumeNext()

    On Error GoTo TestMeResumeNext_Error

    Dim a As Range
    a = 12              'Error 91 here!
    Debug.Print a.Row   'Resume Next goes back here
    Exit Sub

TestMeResumeNext_Error:
    Set a = Range("A14")
    Resume Next
End Sub

Sub TestMeResume()

    On Error GoTo TestMeResume_Error

    Dim a As Long: a = 10
    Dim b As Long: b = 0
    Debug.Print a / b   'Error 11 here the first time
    Exit Sub

TestMeResume_Error:
    b = 5
    Resume
End Sub

Answer 2

The other option here is to inline your error handler. This makes it obvious that you are aware of the potential for a specific line of code to raise an error, and doesn't rely on Resume for flow control. Letting the error go to your handler at the bottom of the function and then jump back into the main line of the code makes it much less obvious what the expected behavior of the loop is.

Sub SkipIteration()
    Dim x As Long

    For x = 5 To -5 Step -1
        On Error Resume Next
        Debug.Print 15 / x
        If Err.Number = 11 Then
            Debug.Print "Expected error on " & x
            On Error GoTo errhandler
        Else
            On Error GoTo errhandler
            Debug.Print "Process the rest of the loop"
        End If
    Next x

    Exit Sub
errhandler:
    Debug.Print "Unexpected error "; Err.Number
End Sub

Even better would be to separate the concerns of your procedures in a way that the portion of the loop that gets executed is a single statement:

Sub SkipIteration()
    On Error GoTo errhandler

    Dim x As Long
    For x = 5 To -5 Step -1
        If TheThingThatCanError(x) Then
            ActionThatDependsOnAbove x
        End If
    Next x

    Exit Sub
errhandler:
    Debug.Print "Unexpected error "; Err.Number
End Sub

Private Function TheThingThatCanError(x As Long) As Boolean
    On Error Resume Next
    Debug.Print 15 / x
    If Err.Number = 11 Then
        Debug.Print "Expected error on " & x
        Exit Function
    End If
    TheThingThatCanError = True
End Function

Private Sub ActionThatDependsOnAbove(x As Long)
    Debug.Print "I don't want this to be printed on error " & x
End Sub

Answer 3

If you want to skip the Error and continue with the next iteration, a possible way is to use GoTo and make some spaghetti code:

Sub SkipIteration()
    Dim x As Long
    For x = 5 To -5 Step -1
        If IsError(Evaluate("15 / " & x)) Then GoTo Skipper
        Debug.Print 15 / x
        Debug.Print x
Skipper:
    Next x
End Sub

Or use embed the whole IsError check in a if and avoid the GoTo. But then you have to go one tab to the right and lose indentation:

Sub SkipIteration()
    Dim x As Long
    For x = 5 To -5 Step -1
        If Not IsError(Evaluate("15 / " & x)) Then
            Debug.Print 15 / x
            Debug.Print x
        End If
    Next x
End Sub