What is Excel VBA On Error GoTo?
To handle errors, we use the VBA On Error GoTo statement. In order to handle errors or unforeseen circumstances arising during the execution of your code, error handling is an essential component of programming. One way to incorporate error handling in VBA is through the use of VBA On Error GoTo.
Let us look at an example. We have a subroutine named “Example.” To define an error handling label called ErrorHandler, we use “On Error GoTo ErrorHandler.” We try to divide ten by 0 inside the subroutine, which results in a “division by zero” error.
When the error occurs, VBA jumps to the ErrorHandler label, where we display a custom error message using MsgBox.
When you execute the above code, it causes a “division by zero” error when it tries to divide ten by 0. It then uses the error handling label to display a message box with the error description “An error occurred: Division by zero.”
Table of contents
Key Takeaways
- VBA On Error statements are used for error handling, allowing you to manage unexpected errors in your code.
- VBA On Error Resume Next allows code execution to continue after an error occurs, “VBA On Error GoTo 0″ turns off error handling, and “VBA On Error GoTo Label” directs the program to a specific label for error handling.
- Be specific in handling errors, reset error handling when necessary, and use the “Err” object to access error details.
- When you use VBA On Error GoTo Line Number, you specify a line number or a label to which the program should jump when an error occurs during the execution of your code.
How to Use VBA On Error Statements?
Using “On Error” statements in VBA involves specifying how errors should be handled when they occur. There are several ways to use “On Error” statements.
Step 1: Open Excel and press ALT + F11 to open the VBA editor.
Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
Step 3: Enable Error Handling
To use “On Error,” your VBA procedure must first enable error handling. To accomplish this, you can use the following statement:
“On Error Resume Next”
This statement allows your code to continue running even if an error occurs. If an error occurs, VBA will skip the line that caused the error and continue executing the following line.
Step 4: Define Error Handling in VBA Behavior
Once error handling is enabled, you can specify how errors should be handled.
There are three common ways to do this:
| On Error Statement | Description |
|---|---|
| 1. On Error Resume Next | Enables error handling and allows the code to continue executing even if an error occurs. Useful for ignoring errors and continuing execution. |
| 2. On Error GoTo 0 | Turns off error handling. Any errors that occur after this statement will not be ignored, and the program will halt with a runtime error message. |
| 3. On Error GoTo Label | Defines a label (usually labeled as LabelName) that the program should jump to when an error occurs. Allows you to create custom error-handling routines. |
Step 5: Create Error-Handling Routines
Error-handling procedures should be created to handle errors efficiently. When an error happens, these procedures are carried out.
These routines can be tailored to log errors, show the user a message, or perform any other necessary action.
For example:
ErrorHandler:
‘ Custom error-handling routine, e.g., display a message or log the error
Step 6: Run the VBA Code by pressing F5 or by clicking on the Run button in the VB editor.
Examples
Example #1 – On Error Resume Next
In this example, “On Error Resume Next” is used to ignore the division by zero error. The code continues executing, and the result is set to 0.
Step 1: In the new module, we first create a new subroutine named “Example1.”
Step 2: Next, we enable error handling with “On Error Resume Next.” It tells VBA to continue executing the code even if an error occurs.
Step 3: In this step, we declare a variable result and attempt to divide 10 by 0. This operation will cause a “division by zero” error.
Step 4: In this step, we show the result using the “MsgBox” function.
Step 5: This line resets error handling to its default behavior, where errors will halt the program with a runtime error message.
Step 6: Now, run the code. When this code runs, “On Error Resume Next,” which enables error handling, permits the program to carry on running even in the event of an error.
When dividing ten by 0, the line MsgBox “Result: ” & result will execute even though the “division by zero” error appears. The error is ignored so that the result will be 0.
After that, “On Error GoTo 0” is used to return error handling to its initial state.
Here is the complete code:
Sub Example1()
On Error Resume Next
Dim result As Integer
result = 10 / 0 ‘ This will cause a division by zero error
MsgBox “Result: ” & result ‘ This line will execute, and result will be 0
On Error GoTo 0 ‘ Reset error handling to default
End Sub
Example #2 – On Error GoTo 0
In this example, “On Error GoTo 0” turns off error handling, and the code halts when the division by zero error occurs.
Step 1: In the new module, we create a new subroutine named “Example2.”
Step 2: This line turns off error handling using “On Error GoTo 0.” Any errors that occur after this line will not be ignored, and the program will halt.
Step 3: Similar to the previous example, we attempt to divide 10 by 0, which triggers a “division by zero” error.
Step 4: Since there is no error handling in place (error handling was turned off in step 1), this line will not execute, and the program will halt when the error occurs.
Step 5: Save the macro and run the code. When this code runs, error handling is disabled with “VBA On Error GoTo 0,” which results in the program stopping when dividing 10 by 0 and displaying an error message titled “division by zero.”
Here is the complete code:
Sub Example2()
On Error GoTo 0 ‘ Turn off error handling
Dim result As Integer
result = 10 / 0 ‘ This will cause a division by zero error
MsgBox “Result: ” & result ‘ This line will not execute due to the error
End Sub
Example #3 – On Error GoTo Label
In this example, “On Error GoTo ErrorHandler” directs the program to the ErrorHandler label when a division by zero error occurs. The error message is displayed to the user.
Step 1: Just like the previous examples, we first create a new subroutine named “Example3.”
Here, we use “On Error GoTo ErrorHandler” to define a label named ErrorHandler to which the program will jump when an error occurs.
Step 2: Here, we declare a variable named result as an VBA integer data type. This variable will be used to store the result of a calculation.
Step 3: In this step, we attempt to perform a calculation by dividing 10 by 0 just like in previous examples. Dividing by zero is mathematically undefined and will trigger a “division by zero” error.
Step 4: This line attempts to display a message box with the result of the calculation. However, due to the error in the previous step, this line will not be executed, and the program will jump to the error handling section.
Step 5: Next, we exit the subroutine. It’s placed here so that if no error occurs, the program can gracefully exit the subroutine.
Step 6: When the “division by zero” error occurs, the program jumps to the ErrorHandler label.
In this labeled section, we use MsgBox to display a custom error message that includes the description of the error obtained from the Err object. The user will see a message like “An error occurred: Division by zero.”
Step 7: When this code is run, it generates a “division by zero” error when it tries to divide 10 by 0. It then uses the error handling label to display a message box with the error description “An error occurred: Division by zero.”
Here is the complete code:
Sub Example3()
On Error GoTo ErrorHandler ‘ Define a label for error handling
Dim result As Integer
result = 10 / 0 ‘ This will cause a division by zero error
MsgBox “Result: ” & result ‘ This line will not execute due to the error
Exit Sub ‘ Exit the subroutine if no error occurred
ErrorHandler:
MsgBox “An error occurred: ” & Err.Description ‘ Display the error message
End Sub
Important Things To Note
- To prevent unexpected error handling in later code, it’s a good idea to reset error handling using “On Error GoTo 0” after utilizing VBA On Error Resume Next or VBA On Error GoTo Label.
- VBA On Error GoTo Next Loop is a concept of using error handling to skip the current iteration of a loop and proceed to the next iteration in case of an error.
- VBA On Error GoTo MsgBox implies using VBA On Error GoTo to direct the program to display a message box when an error occurs. It is a common error-handling technique to notify users about errors.
- VBA On Error GoTo ErrorHandler instructs the application to execute a custom error-handling code by jumping to a label called ErrorHandler when an error occurs. The user can customize this label, and it supports particular error handling.
Frequently Asked Questions (FAQs)
When an error occurs, “VBA On Error Resume Next” permits the code to continue running, and “VBA On Error GoTo” designates a label to jump to. You can more effectively handle various types of errors in your code by combining them.
Yes, you can handle different errors in different ways by using multiple “VBA On Error GoTo” statements in a single procedure. But pay attention to the sequence in which you define them—the first one you come across will be the most important.
Without a “VBA On Error GoTo” statement, VBA will display the default error message, and the program will terminate if an error occurs. The user may experience disruptions from this, and the error handling may be insufficient.
Use a line like “LabelName:” to define a label in your code so that you can create an error-handling routine. Next, to indicate which label to jump to in the event of an error, use “VBA On Error GoTo LabelName.” Write the code to handle the error in the labeled section gracefully.
Recommended Articles
This has been a guide to VBA On Error Goto. Here we explain how to use On Error Goto Statement in VBA to handle errors, with examples, and points to remember. You may learn more from the following articles –
Leave a Reply