On Error Statement in Microsoft Excel VBA

1. What Is the On Error Statement in VBA
2. On Error Goto 0
3. On Error Goto <label>
4. On Error Resume Next
5. Conclusion

Excel VBA provides developers with a variety of different functions and features. This article will learn about the On Error statement in VBA.

What Is the On Error Statement in VBA

The On Error statement in VBA is used to instruct the compiler about what to do next in case of an error. It is a way of performing error handling on the VBA code.

Error handling is the process of anticipating and resolving errors before they occur. This is usually done for runtime errors that occur while the code is being executed, for example, referencing a workbook in Excel that does not exist.

Such errors throw exceptions, which halt the execution of the code. Therefore, to avoid this, we need to anticipate the piece of code that can produce errors and perform proper error handling on them.

One easy way of handling exceptions in Excel VBA is to use the On Error statement. There are three types of On Error statements in VBA having the following syntax:

1. On Error GoTo 0
2. On Error Goto <label>
3. On Error Resume Next

Let us explain them one by one.

On Error Goto 0

The On Error Goto 0 statement is like having no error handling in place because it uses VBA’s default action to show a standard error message when encountering an exception. The error message will display the runtime error’s type and name and the associated number.

It will also provide some options to the user about what to do next with the code. These options are:

1. Continue: Pressing this option will continue the code, ignoring the error. However, it is impossible to do so every time, and the option is greyed out and made non-interactive when the execution cannot be continued.
2. End: This option terminates the program and ends execution.
3. Debug: This option will help you debug the code to find the source of the error and resolve it. Pressing this takes the program to debug mode, starting from the line where the exception was encountered.
4. Help: This option will take the user to the official help pages by Microsoft, providing information about the exception.

Let us run the following code, which will produce an error if Book1.xlsx does not exist in the active workbooks list.

Sub ExampleGoto0()

On Error GoTo 0
Workbooks("Book1.xlsx").SaveAs

End Sub

In error, the following message box will be displayed:

On Error Goto <label>

The On Error Goto <label> statement combines error handling with the Goto statement by instructing the compiler to go to a specific line in the code when an exception occurs.

The line is specified by either a line number or a label. When an error occurs, the program control is taken to the line followed by the label, skipping any code written in between.

A label is declared in VBA using the following syntax:

labelname:

Note: The specified line or label in the Goto statement must be in the same procedure as the On Error Goto statement, or a compile-time error will be generated.

Let us look at how we can use a label to move to a particular line upon error.

Sub ExampleGotoLabel()

On Error GoTo errorhandler
Workbooks("Book1.xlsm").SaveAs
MsgBox "This line will not be executed"

Exit Sub

errorhandler:
Range("A1") = "Error handled"

End Sub

Note: We must use the Exit Sub command right before the label to prevent the lines after the label from executing every time, even when there is no error.

On Error Resume Next

The On Error Resume Next statement skips the line where the error occurred and continues execution from the very next line. It is important to note that this statement does not handle any errors or resolve them; rather, it ignores the erroneous statement as if it does not exist in the code.

Therefore, the On Error Resume Next statement can be very tricky if not implemented correctly. This is because we might skip some lines essential for the code that will be executed, such as variable initializations.

To handle such situations, we use the Err.Number property of the Err object, which keeps track of all the exceptions.

When an exception occurs, the Err.Number is assigned some value; otherwise, it is set as 0. This information can track any exceptions and perform error handling accordingly.

Note: It is recommended to use the On Error Resume Next statement instead of On Error Goto when working with objects.

Let us look at the following code, which will throw an exception if we try to save a workbook that does not exist. Due to the On Error Resume Next statement, the erroneous statement will be ignored.

Sub ExampleResumeNext1()

On Error Resume Next
Workbooks("Book1.xlsx").Save
Range("A1") = "Error skipped"

End Sub

Now, let us look at another example that demonstrates using the Err.Number property to handle situations where skipping a line of code can be harmful.

Sub ExampleResumeNext2()

On Error Resume Next
N = 1 / 0  ' This line will cause an exception because of division by zero

If Err.Number <> 0 Then
N = 1  ' Some default value of N, so that it is not used uninitialized in the following lines
End If

For i = 1 To N
Range("A1") = i
Next i

End Sub

Conclusion

This sums up our discussion on the On Error statement in VBA, which is a useful programming language saving valuable time for Microsoft Office users. We hope you have learned the various ways in which the On Error statement is used for error handling.