What is Excel VBA On Error Resume Next?
VBA On Error Resume Next is a statement used to handle faults in a particular manner. It instructs VBA to carry on the code execution even in the event of a mistake, thereby disregarding the error and proceeding to the subsequent line of code. It can be helpful in situations where you wish to run code regardless of any failures, like when handling big datasets or external data sources.
Let us look at an example to see how to use VBA On Error Resume Next. First, we apply VBA On Error Resume Next, which lets us put errors on hold for the time being. We then try a division by zero operation (1 / 0), which usually raises an error at runtime.
However, due to VBA On Error Resume Next, the code continues executing without halting. Subsequently, we use the “Debug.Print” statement to display “Value: 0” in the Immediate window. It shows that the code does not end abruptly, even when there is a division operation error, allowing us to carry out more tasks.
Table of Contents
Key Takeaways
- VBA On Error Resume Next statement is used to continue executing code even when errors occur, allowing you to handle errors selectively.
- It’s essential to check for errors using “Err.Number” after using VBA On Error Resume Next to handle errors appropriately.
- To enable VBA On Error Resume Next End, you can revert to normal error handling using “On Error GoTo 0” to avoid unintentional error handling in subsequent code blocks.
- Within loops like For or While loops, VBA On Error Resume Next in Loop can be applied. It enables uninterrupted code execution even if errors occur during loop iterations.
How to Use Error Resume Next in VBA?
Using VBA On Error Resume Next is straightforward and will involve the following steps:
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: After creating a subroutine, place the VBA On Error Resume Next statement at the beginning of the code block where you want to handle errors in VBA.
Sub ExampleSubRoutine()
On Error Resume Next
‘ Your code here
End Sub
Step 4: Write the code that you want to execute even if errors occur after the VBA On Error Resume Next statement.
Step 5: After executing the code, you can check if an error occurred by using the Err object.
If Err.Number <> 0 Then
‘ Handle the error here
End If
Step 6: Use the VBA On Error GoTo 0 statement to go back to the standard error-handling behavior after a certain section of code.
On Error GoTo 0
Step 7: Run the code after saving the macro. You will notice that the mistakes are ignored as a result of the VBA On Error Resume Next line.
Examples
Example #1 – Handling Missing Worksheet
In this example, we try to reference “Sheet2,” a worksheet. If it cannot be located, the error is ignored using the VBA On Error Resume Next before determining whether the reference is “Nothing.”
Step 1: In this step, we define the Example1 subroutine and include the On Error Resume Next statement to allow the code to continue running even if errors occur.
Step 2: Here, we declare a variable “ws” to represent a worksheet. Then, we attempt to set the “ws” variable to refer to a worksheet named “Sheet2” within the workbook using “ThisWorkbook.Sheets(“Sheet2”).” If the worksheet exists, the reference will be successfully set; otherwise, “ws” will be “Nothing.”
Step 3: After attempting to set the worksheet reference, we use On Error GoTo 0 to reset the error handling to its default behavior.
Step 4: In this step, we check if the ‘ws’ variable is “Nothing” (indicating that the worksheet was not found). If so, it means the worksheet was found, and a message box displays “Worksheet found: ” followed by the worksheet name.
If ws is Nothing, it means the worksheet was not found, and a message box displays “Worksheet not found.”
Step 5: The output of this code will be a message box with the text “Worksheet found: Sheet2” if the worksheet “Sheet2” is present in the workbook.
However, in this case, the worksheet does not exist, and therefore, it will display “Worksheet not found.”
Here is the complete code:
Sub Example1()
On Error Resume Next
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet2”)
On Error GoTo 0
If Not ws Is Nothing Then
MsgBox “Worksheet found: ” & ws.Name
Else
MsgBox “Worksheet not found.”
End If
End Sub
Example #2 – Ignoring Division by Zero
In this example, the code tries to divide by zero. VBA On Error Resume Next ignores the error and then uses “Err.Number” to determine whether an error occurred.
Step 1: In the new module, we define the Example2 subroutine and include the On Error Resume Next statement to allow the code to continue running even if errors occur.
Step 2: Here, we declare a variable result to store the result of a division. We attempt to divide 10 by 0, which will trigger a “Division by zero” error.
Step 3: After performing the division (which would result in an error), we use On Error GoTo 0 to reset the error handling to its default behavior.
Step 4: In this step, we check if an error occurred by examining Err.Number. If Err.Number is not equal to 0, it means an error occurred during the division, and a message box displays “Error: ” followed by the error description.
If Err.Number is 0, it means no error occurred, and a message box in VBA displays “Result: ” followed by the value of result.
Step 5: This code, when executed, shows “Result: Infinity” because we have used the VBA On Error Resume Next, which causes VBA to regard division by zero as returning a special value “Infinity” rather than triggering a runtime error.
Here is the complete code:
Sub Example2()
On Error Resume Next
Dim result As Double
result = 10 / 0
On Error GoTo 0
If Err.Number <> 0 Then
MsgBox “Error: ” & Err.Description
Else
MsgBox “Result: ” & result
End If
End Sub
Example #3 – Looping through Worksheets
In this example, the code tries to print the value of cell A1 on each worksheet as it iterates through the entire workbook. VBA On Error Resume Next in Loop feature makes sure that the loop keeps running even when there are accessing failures on specific sheets.
Step 1: Here, we first define the Example3 subroutine and include the On Error Resume Next statement to allow the code to continue running even if errors occur.
Step 2: Here, we declare a variable “ws” to represent a worksheet. Then, we use a “For Each” loop to iterate through all the worksheets in the workbook (ThisWorkbook.Sheets).
Step 3: Now, inside the loop, we use “Debug.Print” to print the name of each worksheet and the value of cell A1 in that worksheet.
Step 4: After completing the loop, we use On Error GoTo 0 to reset the error handling to its default behavior.
Step 5: When you run this code, it will loop through all worksheets in the workbook. For each worksheet, it will print its name and the value of cell A1 in the Immediate window.
The output will be a list of worksheet names and their corresponding values in the Immediate window.
Here is the complete code:
Sub Example3()
On Error Resume Next
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Debug.Print ws.Name & “: ” & ws.Cells(1, 1).Value
Next ws
On Error GoTo 0
End Sub
Important Things To Note
- After executing code with VBA On Error Resume Next, it is crucial to check for errors using “Err.Number” to handle them appropriately.
- To deactivate VBA error handling or to enable VBA On Error Resume Next Turn Off, you can use the statement “On Error GoTo 0.”
- While debugging, be cautious, as On Error Resume Next can make it harder to identify and troubleshoot errors.
- VBA On Error Resume Next for one line can be used on a single line of code in VBA to temporarily ignore errors for that specific line. It allows you to handle errors immediately after that line.
Frequently Asked Questions (FAQs)
VBA On Error Resume Next may not work as expected due to several reasons:
• Placing the On Error Resume Next statement at the wrong location within your code can lead to it not working as intended. Ensure that it is positioned before the code where you anticipate errors.
• If your VBA project has a global error handler which conflicts with VBA On Error Resume Next, it might override the local error handling.
• When using On Error Resume Next within nested subroutines, errors in inner subroutines may not be caught by the outer error handling.
• If your code has logic errors that prevent it from reaching the VBA On Error Resume Next statement, it may seem like it’s not working.
After using VBA On Error Resume Next, you can check if an error occurred by examining the “Err” object. Specifically, you can use “Err.Number.” If “Err.Number” is not equal to zero, it indicates that an error has occurred. You can also access additional error information using “Err.Description” and other properties of the “Err” object.
Using the VBA On Error Resume Next carries some risks:
• By ignoring errors, you might mask critical issues in your code, leading to unexpected results or data corruption.
• When errors are silently ignored, debugging becomes challenging, as you may not immediately know where and why an error occurred.
• If you don’t handle errors appropriately, your code may continue executing despite errors, potentially causing further issues or resource wastage.
To revert to normal error handling after using VBA On Error Resume Next, you can use the statement “On Error GoTo 0.” This statement clears the error state and restores VBA’s default error-handling behavior. It’s essential to use this statement when you no longer want to ignore errors and want to handle them conventionally.
Recommended Articles
This has been a guide to VBA On Error Resume Next. Here we learn How to Use Error Resume Next in VBA along with examples. You can learn more from the following articles –
Leave a Reply