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:
Note: You can find the complete documentation for the Exit statement in VBA here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Create Folders Using VBA
How to Delete Folders Using VBA
How to Delete Files Using VBA