VBA - Error Trapping

When an error occurs while executing the code, VBA treats that error as fatal and stops executing the code then and there, with an error alert message to the user. While at times this is what you may want, most of the times you will just have to continue running the code and do other jobs that macro was supposed to do. For this, we have to use what is called an Error Trapping. VBA provides ways to tackle with that error without interrupting the code. Let’s see what options we have to do that.

On Error Statement

This statement enables error-handling routine and you can specify from where the code should continue executing. We have 3 options to provide such location.

On Error Resume Next

This tells VBA to ignore the error and continue running the code from immediate next line of code following the line where the error occurred.

This error handler will be active through out your code. No matter how many times errors occurr in your procedure, VBA always executes the next line. But be careful, with this line, VBA ignores any kind of error. If you don’t take care, it may result into ignoring few errors that should not be ignored.

On Error GoTo line

This enables the error-handling routine to start at the line specified in the argument. The line argument is any line label or line number. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.

Note that, on first occurrence of first error, VBA starts executing the code from line given as argument and continues executing the lines below it till the End Sub.

Once executed, error handler will be disabled for further use. That means, On Error GoTo statment can be used for only one error in a procedure.

On Error GoTo 0

Disables any error handler that is in the current procedure!

Let's see the error handling through an example. We will deliberately do something that causes an error. How about try to enter some value in a protected sheet and that will cause an error? Copy/Paste the below code to your module! Protect the active sheet. Click anywhere on the code in VBA and press F8 repeatedly to run the macro line by line. On each press of F8, you can notice where the focus is going.

Example –On Error Resume Next

Sub Test()
On Error Resume Next 'Defining the error trapping, on error focus should go to next line
ActiveCell.Value = 1000 'try to enter some random value in active cell
If Err.Number <> 0 Then 'check if error occurred?
    MsgBox ("Error Occurred") 'Yes? then message box saying so
Else
    MsgBox ("Done") 'No? Then all done.
End If
End Sub

Run this code by repeatedly pressing F8. Now you see that no matter what, focus will always go to the next line. We are trapping the error by If-Then-Else-End If statements.

Example –On Error Goto line

Sub Test()
On Error GoTo ErrorHandler 'Defining the error trapping, focus should go to a line named ErrorHandler below
ActiveCell.Value = 1000 'try to enter some random value in active cell
Exit Sub 'If no error, procedure should stop here

ErrorHandler: MsgBox ("Sorry, Error " & Err.Number & " Occurred") 'This line contains what to do in case of error
End Sub

If you noticed, from ActiveCell.Value = 1000 line, focus will change to ErrorHandler, as there will be an error when executing that line and we asked VBA to go to ErrorHandler on error occurrence. On one more press F8, a message box is shown informing the error number to the user.

Err Object

Wherever there is an error, VBA keeps that error information in Err object. You can use this information to know more about the type of error. You can in turn display the information to the user as we did in our earlier code. Err.Number provides the system assigned number of this error. There is also Err.Description will provide the description of the error. See other options available under Err object and explore.