VBA On Error

What is Excel VBA On Error Statement?

In VBA, the On Error statement is used to handle runtime errors that may occur during the execution of a macro or procedure in Excel. When an error occurs, VBA can either halt the execution of the code, display an error message, or handle the error and continue with the following line of code. The On Error statement enables the programmer to define how VBA should respond to errors, ensuring the code remains robust and can handle unexpected situations.

Let us look at an example. In this example, the VBA macro attempts to access an element in an array (arr) that doesn’t exist. Array indices in VBA start from 0, so trying to access arr(3) will result in an “Index out of range” runtime error.

Using On Error GoTo ErrorHandler, the code handles the error and displays a user-friendly message “An error occurred: Subscript out of range.” The code continues execution after the error, ensuring a smoother user experience.

What is an Error?

In the context of VBA, an error refers to an unexpected or exceptional condition that disrupts the normal execution flow of a program. Errors can occur for various reasons, such as invalid data input, division by zero, trying to access an unavailable resource, or encountering a syntax error in the code. These errors may lead to program termination or incorrect results if handled improperly.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Types of On-Error Statements

There are three primary types of VBA On Error statements:

• On Error Resume Next: The VBA On Error Resume Next statement instructs VBA to continue executing the code even if an error occurs.

Usually, when it encounters an error, VBA will ignore it and move on to the next line of code. VBA On Error go to following line handling can be risky if not used carefully, as it may hide critical errors and lead to unexpected results.

• On Error GoTo Label: The VBA On Error GoTo Label statement redirects the execution flow to a specific label (a defined section of code) when an error occurs.

It allows the programmer to handle the error gracefully at the designated location. It is considered a more structured approach to error handling and helps improve code robustness and maintainability.

• On Error GoTo 0: The VBA On Error GoTo 0 resets the error handling behavior to the default state, treating runtime errors as unhandled exceptions.

It disables active error handling, displaying the standard error message and halting code execution upon encountering an error.

Top 3 Ways to Handle Errors in VBA

Now that we have seen different ways of handling errors in VBA let us look at a few examples of how to implement them.

Example #1 – On Error Resume Next:

This method is used when you want the code to continue executing even if it encounters an error. With “On Error Resume Next,” VBA will ignore the error and move to the following line of code.

The VBA On Error Resume Next statement allows you to continue executing the code without terminating when an error occurs. However, this approach should be used cautiously, as it may hide potential issues and lead to unexpected behavior. Here’s how to use it:

• Step 1: In a new Excel workbook, press “ALT + F11” in Excel to open the Visual Basic for Applications (VBA) editor.

• Step 2: Insert a new Sub procedure: In the VBA editor, click “Insert” in the top menu and select “Module” to insert a new module.

• Step 3: In the new module, declare a subroutine named “Example_OnErrorResumeNext.”

• Step 4: Next, we declare a variable named the result of the VBA data type Double. This result variable stores the result of a division operation performed in the next step.

• Step 5: In this line, we write VBA code to divide ten by 0 and assign the result to the result variable. However, division by zero is mathematically undefined, so this operation causes a runtime error in VBA.

• Step 6: In this line, we write the code to display a message box showing the result of the division operation.

Here, the VBA On Error Msgbox should display the text “Result: ” followed by the value of the result variable.

However, since a runtime error occurs in the previous line, this line of code will not be executed.

Let us save the module and try to execute the code.

When you execute the above code, it attempts to perform a division by zero operation, which raises a runtime error and displays a message box with an undefined “Result” due to the error.

• Step 7: To handle this error, we will use the VBA On Error statement, such as “On Error Resume Next,” which tells VBA to ignore the error and continue with the following line of code.

• Step 8: Save the updated macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the “Example_OnErrorResumeNext” macro from the list and click “Run.”

• Step 9:  Once you execute the macro, you will not see the run time error; instead, you will see a message box with “Result: inf.”

Here is the full code:

Sub Example_OnErrorResumeNext()
On Error Resume Next
‘ Code that may cause an error
Dim result As Double
result = 10 / 0 ‘ Division by zero, but the code continues
MsgBox “Result: ” & result ‘ This line won’t execute due to the error
End Sub

Example #2 – On Error GoTo Label:

This method directs the program to a specific label where you handle the error.

When an error occurs, the On Error GoTo Label statement directs VBA to jump to a specific label (a predefined section of code). It allows the programmer to handle the error appropriately. In this example, an error is generated when opening a non-existent workbook. Still, instead of stopping execution, VBA jumps to the “ErrorHandler” label and displays a message box describing the error.

• Step 1: In the new module, starts by declaring a subroutine named “Example_OnErrorGoToLabel1.”

• Step 2: In this line of code, we declare a variable “wb” of type Workbook. This variable stores the workbook object returned when we try to open a workbook.

• Step 3: In the following line, we write a “VBA On Error” statement that tells VBA to jump to the “ErrorHandler” label if an error occurs in any subsequent line of code in this subroutine.

• Step 4: Now, we write a code to open a workbook named “NonExistentWorkbook.xlsx” and assign it to the “wb” variable.

Since this workbook does not exist, this operation results in a runtime error.

Due to the “On Error GoTo ErrorHandler” statement, when this error occurs, the execution jumps to the “ErrorHandler” label instead of stopping the program.

• Step 5: We write the “VBA On Error Exit Sub” statement next. If there were no errors in the code, this line ensures that the code following the “ErrorHandler” label does not get executed.

• Step 6: Now, we write the “ErrorHandler” label. If an error occurs in the code, execution jumps to this point. The line of code inside this label displays a message box describing the error.

• Step 7: Save the updated macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” Example_OnErrorGoToLabel1 ” macro from the list and click “Run.”

• Step 8: Once you execute the macro, you will see a message box that says the required workbook is unavailable.

Here is the full code:

Sub Example_OnErrorGoToLabel1()
Dim wb As Workbook
On Error GoTo ErrorHandler
‘ Attempt to open a workbook that doesn’t exist
Set wb = Workbooks.Open(“NonExistentWorkbook.xlsx”)
‘ Normal code continues here
‘ …
Exit Sub
ErrorHandler:
‘ Display a message box with error description
MsgBox “An error occurred: ” & Err.Description
End Sub

Example #3 – Print Error Number in VBA

This method involves displaying the error number and description, which can be helpful for debugging purposes.

You can use the Err object to print the error number when an error occurs.The Err object provides information about the last runtime error that occurred.

Let’s consider an example where we try to access a worksheet that doesn’t exist in the active workbook. It will generate an error, and we’ll handle it using the “On Error Resume Next” statement.

• Step 1: In the new module, start by declaring a subroutine named “Example_PrintErrorNumber.”

• Step 2: Next, we declare a variable “ws” of type Worksheet. This variable is stores the worksheet object when we try to access a worksheet.

• Step 3: Now, we provide a “VBA On Error” statement. It instructs VBA to continue with the next line of code if an error occurs, effectively ignoring it

• Step 4: In the following line of code, we try to access a worksheet named “NonExistentSheet” in the active workbook and assign it to the “ws” variable.

As this worksheet does not exist, this operation results in a runtime error.

However, this error is ignored due to the “On Error Resume Next” statement and the execution continues with the next line of code.

• Step 5: Next, we write a code to check if an error occurred.

If an error occurs (in which case, “Err.Number” is not 0), a message box shows the error number and description.

Then, the “Err.Clear” statement resets the Err object, clearing the error information so it doesn’t affect further error handling.

• Step 6:  Save the updated macro and close the VBE editor. Press “ALT + F8” to open the “Macro” dialog box.

Select the ” Example_PrintErrorNumber” macro from the list and click “Run.”

• Step 7: Once you execute the macro, you will see a message box that provides the Error number and description.

Here is the full code:

Sub Example_PrintErrorNumber()
Dim ws As Worksheet
On Error Resume Next
‘ Attempt to access a worksheet that doesn’t exist
Set ws = ThisWorkbook.Worksheets(“NonExistentSheet”)
‘ Check if an error occurred
If Err.Number <> 0 Then
MsgBox “Error Number: ” & Err.Number & vbCrLf & “Error Description: ” & Err.Description
Err.Clear ‘ Clear the error for further error handling
End If
End Sub

Important Things to Note

• Using On Error Resume Next can be risky, as it may mask critical errors and make debugging difficult.
• Always use On Error GoTo Label or other structured error-handling techniques to handle errors effectively.
• Error handling is essential in any program to prevent crashes and data loss. However, it should not be used to hide errors or to continue execution after a fatal error.
• When using the On Error GoTo Label, include an Exit Sub or Exit Function statement within the error handler to prevent executing the code below the handler in case of an error.
• Remember to clear the error using Err.Clear after handling an error to reset the error object for further error detection.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA On Error. Here we learn the types of On Error statements in Excel VBA and the top 3 ways to handle them, along with examples. You can learn more from the following articles –

• VBA Type Mismatch Error
• VBA 1004 Error
• VBA IsEmpty