VBA: How to Exit Sub on Error

You can use the Exit Sub statement in VBA to exit a sub procedure when an error is encountered.

Here is one common way to use this statement in practice:

Sub DivideValues()

Dim i As Integer
On Error GoTo ErrorMessage

For i = 1 To 10
    Range("C" & i) = Range("A" & i) / Range("B" & i)
Next i
    
Exit Sub

ErrorMessage:
    MsgBox "An Error Occurred"
    Exit Sub
    
End Sub

This particular macro attempts to divide each value in the range A1:A10 by the corresponding value in the range B1:B10.

If an error occurs (e.g. we attempt to divide by zero) then the On Error GoTo statement tells VBA to go to ErrorMessage, which says to produce a message box telling the user an error occurred and to end the sub procedure by using the Exist Sub statement.

The following example shows how to use this syntax in practice.

Example: How to Exist Sub on Error in VBA

Suppose we have the following list of values in columns A and B:

Now suppose we create the following macro to divide each value in column A by the corresponding value in column B and display the results in column C:

Sub DivideValues()

Dim i As Integer

For i = 1 To 10
    Range("C" & i) = Range("A" & i) / Range("B" & i)
Next i
    
End Sub

When we run this macro, we receive the following error:

We receive this error because in the fourth line of the Excel sheet we attempted to divide by zero.

If we’d like to simply exit the sub procedure when this error occurs, we can use the Exit Sub statement as follows:

Sub DivideValues()

Dim i As Integer
On Error GoTo ErrorMessage

For i = 1 To 10
    Range("C" & i) = Range("A" & i) / Range("B" & i)
Next i
    
Exit Sub

ErrorMessage:
    MsgBox "An Error Occurred"
    Exit Sub
    
End Sub

When we run this macro, the sub performs all of the division problems it can before it encounters an error and then a message box appears that tells us an error occurred and VBA simply exists the sub procedure:

VBA on error exit sub