What is Excel VBA On Error Goto 0?
The VBA On Error Goto 0 is a statement used to turn off or disable error handling in a procedure. In VBA, error handling allows you to deal with runtime errors that may occur during the execution of your code.
The On Error Goto 0 statement resets the error-handling behavior to the default, which means that if an error occurs, VBA will stop the execution of the code and display the default error message. It effectively turns off any custom error handling that may have been previously set up using the On Error Resume Next or On Error Goto statements.
Consider the following example:
Here, a simulation of the “subscript out of range” error is made. An array with three elements is created, but the 4th place of the array is called, which does not exist and hence creates an error. This error is caught and printed, after which the error value is set to the default value, 0, with the “On Error GoTo 0” statement. The output for this is shown below.
Table of Contents
Key Takeaways
- On Error Goto 0 is a statement in VBA that resets the error-handling behavior to its default state.
- It turns off or disables any custom error handling that might have been set up using On Error Resume Next or On Error Goto [label].
- After using On Error Goto 0, if an error occurs in the code, VBA will revert to its default error-handling behavior. This typically involves halting the execution of the program and displaying a standard error message.
- It is often used when you want to apply custom error handling only to specific sections of code and revert to the default error handling for the rest.
How to use On Error GoTo 0 Statement?
You can follow the steps below to implement the VBA On Error GoTo statement.
Step 1: Open the Excel Workbook and select the Developer tab in the Excel toolbar. Select “Visual Basic”. It opens the VBA Editor, where you can start coding.
Step 2: In the VBA Editor, in the toolbar, click the “Insert” button and select the “Module” option.
Step 3: Write a subroutine to perform VBA’s error handling.
Step 4: Before starting the statements, start error handling by calling the VBA On Error GoTo 0 Resume Next.
Step 5: Initialize an Integer to open and close the File Explorer.
Step 6: Assign the integer variable the FreeFile keyword. With this, you can open and view the File Explorer in your system from Excel.
Step 7: Try to open a non-existent file in your system with the file explorer in Excel VBA.
Since this file does not exist, it will throw in an error. This error is ignored due to declaring “On Error Resume Next”.
Step 8: Check for any errors by checking if the error number is 0. If the VBA On Error GoTo 0 Err.Number is not 0, it means there is an error.
Step 9: Print the error number and description in a message if the If-statement is true.
The vbNewLine is used to write the statement written after this constant to be printed in the following line. After the Message box function, perform the VBA On Error Goto 0 Err.Clear.
Step 10: Reset the Error parameter by setting it back to default.
Code:
Sub ExampleOnErrorHandler()
On Error Resume Next
Dim fileNumber As Integer
fileNumber = FreeFile
Open “NonExistentFile.txt” For Input As fileNumber
If Err.number <> 0 Then
MsgBox “Error opening file: ” & Err.Description & vbNewLine & _
“Error number: ” & Err.number
Err.Clear
End If
On Error GoTo 0
End Sub
Step 11: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Editor. Choose the subroutine to run, and a message box pops up to show the error type and number.
Now, you’ve learned how to use VBA On Error GoTo 0 to perform error handling.
Examples
You can see how to effectively handle errors by viewing some interesting examples below.
Example #1
Consider an application where you want to divide numbers. You must handle the VBA division by zero error. With the “On Error GoTo 0” statement, you can bypass the error and also catch and display it. You can see the differences in execution below when there is error handling done and when there is none.
Step 1: Create a subroutine to catch the division by zero error.
Step 2: Write the code to continue running the subroutine despite errors in the code.
Step 3: Divide a number by 0 to catch the error, as shown.
Step 4: Check if the error number of the subroutine is not equal to zero. If so, it means there is an error in the code or subroutine.
Step 5: Print the error description in a message box and clear the error after printing to erase the error cache.
Step 6: Reset the VBA Error number to 0.
Step 7: Perform another division by 0 error without the statement “On Error GoTo 0”.
Code:
Sub EgErrorHandler1()
On Error Resume Next
Dim result As Integer
result = 1 / 0
If Err.number <> 0 Then
MsgBox “An error occurred: ” & Err.Description
Err.Clear
End If
On Error GoTo 0
Dim value As Integer
value = 2 / 0
End Sub
Step 8: Press “F5” to run the code and select the subroutine you want to run. With the “On Error GoTo 0”, we get:
With no error statement for the second expression, we get an error.
Example #2
In this example, we will deal with a type mismatch error while trying to insert a string variable into an Integer array.
Step 1: Initialize a subroutine to perform Error handling for VBA Type mismatch errors. As seen before, to run the code despite errors, you use the “On Error Resume Next” before declaring the statement.
Step 2: Declare an integer array in VBA and try to input a string variable.
Step 3: Check for any errors in the subroutine by checking if the Error number is zero or not.
If it is not equal to 0, it means that there is an error.
Step 4: Print the error description and number in a message box in case the If-statement is true.
Clear the Error cache after printing it.
Step 5: Disable the error handling thereafter.
Step 6: Perform a division expression to run the code. Print the quotient in the Immediate tab.
Code:
Sub EgErrorHandler2()
On Error Resume Next
Dim myArray(1 To 3) As Integer
myArray(2) = “NotANumber”
If Err.number <> 0 Then
MsgBox “An error occurred: ” & Err.Description & vbNewLine & _
“Error number: ” & Err.number
Err.Clear
End If
‘ Disable error handling with On Error Goto 0
On Error GoTo 0
Dim result As Double
result = 10 / 2
Debug.Print result
End Sub
Step 8: Run the code. You’ll get a Message Box showing the error.
After closing the message box, the quotient is printed in the Immediate tab.
Example #3
In this example, you are simulating an invalid procedure call by calling a non-existent collection index. It is unique to the VBA Collection variables.
Step 1: Name the subroutine trying to replicate the invalid procedure call error.
Step 2: Initialize the Error handling by skipping the lines of error which may stop the code from running.
Step 3: Declare a collection in the VBA code. Declare a new collection to not add values to any existing collections.
Step 4: Provide the keys and values for the elements in the collection as shown.
Step 5: Call a variant variable and call the value of a non-existing key to simulate the error.
Step 6: Check for the existence of errors using an If-condition in VBA.
Step 7: Print the error number and description if there is an error. It will execute the If statement.
Step 8: Reset the error value or turn off the error handling with the statement “On Error GoTo 0”.
Code:
Sub EgErrorHandler3()
On Error Resume Next
Dim myCollection As Collection
Set myCollection = New Collection
myCollection.Add “Item 1”, “Key1”
myCollection.Add “Item 2”, “Key2”
Dim item As Variant
item = myCollection(“Key3”)
‘ Check for errors
If Err.number <> 0 Then
MsgBox “An error occurred: ” & Err.Description & vbNewLine & _
“Error number: ” & Err.number
Err.Clear
End If
On Error GoTo 0
End Sub
Step 9: Run the above subroutine by pressing the green arrow button. A message box will pop up, as shown below.
Important Things To Note
- Use On Error Goto 0 when you want to revert to the default error-handling behavior for specific sections of your code, especially after using On Error Resume Next or custom error handling.
- If you’ve used On Error Resume Next to suppress errors, use On Error Goto 0 to clear any stored error information before potentially encountering new errors.
- Use On Error Goto 0 in conjunction with error-checking code to handle specific errors within a limited scope.
- If you’ve temporarily disabled error handling with On Error Goto 0, make sure to reset it to its default state before exiting the procedure to ensure consistent error handling across different parts of your code.
- If you turn off error handling with On Error Goto 0, ensure that you have proper error-checking mechanisms in place to handle unexpected errors, either with custom error handling or by allowing the default error handling to take effect.
Frequently Asked Questions (FAQs)
● Check for any syntax errors in your VBA code.
● Ensure On Error Goto 0 is in the same procedure or block where error handling is needed.
● On Error Goto 0 won’t work if there’s no active error to handle.
● Verify that Err.Clear is used before On Error Goto 0.
● Avoid using globally; use within specific procedures or functions.
● In debugging, error handling may not work as expected.
● Check if there are issues with the specific Excel or VBA environment.
● Resets error handling to default, allowing VBA to handle errors as usual.
● Temporarily disables custom error handling for specific code sections.
● Clears any stored error information using VBA On Error GoTo 0 Err.Clear.
● Useful for default error handling for certain code blocks.
● Enables isolation of error-handling behavior to specific parts of the code.
● Often follows On Error Resume Next to manage errors in a controlled manner.
Yes, It Can. VBA On Error Goto 0 can be used outside error-handling code blocks.
● It resets error handling for the entire procedure or module.
● VBA On Error GoTo 0 returns to default error-handling behavior globally.
● Use it judiciously as it impacts the entire scope of error handling.
● It is often used after handling specific errors with On Error Resume Next.
● VBA On Error statement of this type is commonly placed at the end of a code block after specific error handling.
Yes, “On Error Goto 0” clears the error variables in VBA. When you encounter an error in VBA, the Err object stores information about the error, such as the error number, description, and source. Using “On Error Goto 0” not only resets the error-handling behavior to its default state but also clears the error information stored in the Err object. This ensures that any previous error information is no longer available, and the Err object is in a clean state.
Consider the example:
.
In this example, Err.Clear is used to explicitly clear the error information, but when “On Error Goto 0” is encountered, it achieves the same result by resetting the error state to its default.
Recommended Articles
This has been a guide to VBA On Error GoTo 0. Here we learn How to use On Error GoTo 0 Statement along with examples. You can learn more from the following articles –
Leave a Reply