In Microsoft Excel VBA, error handling is one of the critical tasks. If you are a coder, you know the importance of error handling to build a perfect application. Any blunder in a statement can hamper your VBA code in a lot of ways. So, you must be cautious to handle those errors while executing a VBA code. There are a lot of run-time errors you can face while using the VBA code in Excel. To solve one of them, we use the On Error Resume Next statement.
In this tutorial, you will learn to use the On Error Resume Next statement in Excel VBA. This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.
Error Handling in Excel VBA
While working with Microsoft Excel VBA, you will face a lot of errors in your sub-procedure. When VBA cannot execute a statement, it throws a run-time error.
Excel automatically deals with these errors, so when a run-time error emerges, it displays a default error message like the following:
Now, you can deal with these variously with some VBA statements. I will discuss them in later sections.
On Error Statements in VBA
To handle run time errors, we instruct Excel with the On Error statement. It decides what kind of operations we want to do next immediately. Basically, we disable these errors by these kinds of error handling.
We use three kinds of On Error statements(syntax) in Excel VBA.
- On Error GoTo line
- On Error Resume Next
- On Error GoTo 0
When you find an error, use the On Error command. Because if you don’t utilize an On Error declaration, those run-time errors will be disastrous. It will show an error prompt and stop the execution.
When we use an On Error statement, we turn on an “enabled” error handler. The “active” error handler is an enabled handler that starts the error-handling operation. If a mistake arises while an error handler is involved, the current method’s error handler can’t endure the error. After that, the control returns to the calling procedure.
If the calling process has an enabled error handler, it gets triggered to manage the error. If your calling system’s error handler is correspondingly engaged, control gives back via earlier calling procedures until it finds an enabled but inactive error handler. If it can not find any idle enabled error handler, this means the error is catastrophic at its occurring point.
Each time the error handler gives authority back to a calling procedure, that procedure evolves the existing procedure. Execution restarts in the present procedure at the moment selected by the Resume statement when an error handler handles errors in any procedure.
‘On Error Resume Next’ in Excel VBA
Now, the On Error Resume Next statement tells VBA to ignore any lines of code having errors and proceed immediately to the following line of code. After that, the Excel VBA codes will skip the line or lines that contain errors in them, and move on to the following sequence of code.
The On Error Resume Next statement forces implementation to resume with the command instantly pursuing the line of codes that induced the run-time error. This statement permits an execution to skip even if there is a run-time error. If you think a particular line of code can produce an error, place the error-handling routine there rather than putting it in another location within the procedure. The On Error Resume Next statement becomes idle when your code calls another procedure. So, when you need a matched error handling in that routine, you have to run an On Error Resume Next command in each named pattern.
It is reasonable when the line of code that you can skip is not necessary to the flourishing running of the macro. But remember, it can be detrimental if you use it incorrectly as it may provide unintended results.
The On Error Resume Next statement doesn’t fix the runtime errors. It basically ignores the errors where your VB execution will resume from the statement that has generated the runtime error.
Take a look at the following code:
Sub divide() MsgBox 5 / 0 MsgBox 5 / 2 End Sub
We tried to divide 5 with 0 and 1. Let’s run the code. It will show the following output:
It produces a run-time error. We can not divide a number by 0. When you debug the code, you will see the following:
When the VB program finds an error, it immediately stops the procedure. It doesn’t execute the following line.
Now, let’s implement the On Error Resume Next statement before the error statement:
Sub divide() On Error Resume Next MsgBox 5 / 0 MsgBox 5 / 2 End Sub
After running the code, you will see the following:
As you can see, VBA ignores the line that produces the error and proceeds immediately to the following line of code. In this way, you can use the On Error Resume Next statement to handle the error in Excel VBA.
In the following sections, I am going to provide you with two examples of the On Error Resume Next statement that you can implement into your Excel worksheet using VBA. I recommend you learn and apply all these to your workbook. It will definitely increase your Excel knowledge.
1. ‘On Error Resume Next’ Statement to Hide Worksheets in Excel VBA
Now, in this example, I will show you a VBA code that will hide all the worksheets of your active workbook.
Take a look at the following screenshot:
Here, we have four worksheets. We will hide all of them using the following VBA code:
Sub hide_all_sheets() Dim copies As Worksheet For Each copies In ActiveWorkbook.Sheets copies.Visible = False Next copies End Sub
When you execute the following code, you will see the following run-time error:
Excel shows this error because you can not hide all the sheets in a workbook. So, you have to ignore the error. To do this, you have to implement the On Error Resume Next statement in your line of code.
Sub hide_all_sheets() Dim copies As Worksheet On Error Resume Next For Each copies In ActiveWorkbook.Sheets copies.Visible = False Next copies End Sub
After completing the execution of the VBA code, you will see the following output:
In the end, you won’t see any errors after execution. So, our On Error Resume Next statement worked really fine in the VBA code.
2. Applying Excel VLOOKUP Function with ‘On Error Resume Next’ in VBA
Take a look at the following screenshot:
Here, you can see some people’s names and their ages. In the adjacent table, we will use the VLOOKUP to find the person’s name and age.
Type the following code to do this:
Sub VLOOKUP_Function() Dim i As Long For i = 5 To 9 Cells(i, 6).Value = WorksheetFunction.VLookup(Cells(i, 5), Range("B:C"), 2, 0) Next i End Sub
Now, run the macro. You will see the following error:
Now, this is a run-time error. Why does it happen? Take a look at the dataset again:
As you can see, there is no data for “Aaron” and “Emma”. That’s why it only executes the VLOOKUP for the first entry. After that, it stops the execution. Now, if you want to ignore the error and proceed to find the rest of the ages, use the On Error Resume Next statement.
Sub VLOOKUP_Function() Dim i As Long On Error Resume Next For i = 5 To 9 Cells(i, 6).Value = WorksheetFunction.VLookup(Cells(i, 5), Range("B:C"), 2, 0) Next i End Sub
After running the VBA code, you will see the following output:
As you can see, by applying the On Error Resume Next command, we ignored the error and found the rest of the persons’ ages. Our VBA code didn’t find any data of Aaron and Emma. That’s why it ignored those values and returned the rest of the values in the Excel worksheet.
Turn Off ‘On Error Resume Next’ with Excel VBA
Now, you may be in a situation where you want to ignore errors for a particular segment of the VBA code. Remember, if you use the On Error Resume Next statement in a VBA code, it will skip all the errors after that. Now, if you want to turn that off and enable the error handling for another segment, use On Error GoTo 0. It will enable error handling again.
To ignore errors
On Error Resume Next
// lines of codes
To turn on Error handling
On Error GoTo 0
//lines of codes
Take a look at the following VBA code:
Sub error_handling() Dim i As Long 'to ignore error On Error Resume Next For i = 5 To 9 Cells(i, 6).Value = WorksheetFunction.VLookup(Cells(i, 5), Range("B:C"), 2, 0) Next i 'to turn off on error resume next On Error GoTo 0 MsgBox i / 0 End Sub
We used the code previously for the VLOOKUP function. Here added an extra chunk of code to demonstrate you. Our code will ignore the errors while performing the VLOOKUP but it will activate the error handling after the On Error GoTo 0 statement.
VBA ‘On Error GoTo’ Statement
Previously I discussed handling the errors using the On Error method. Our whole article was all about On Error Resume Next. Now, there are also two types of error handling that I will discuss in the following sections.
1. VBA On Error GoTo 0
The On Error Goto 0 statement is Excel’s built-in setting if your codes don’t have an error handler in them. It basically implies that when VBA finds an error with On Error GoTo 0, it will halt running the code and show its traditional error message box.
On Error GoTo 0 statement basically turns off the error handling in the present procedure. It doesn’t define line 0 as the beginning of the error-handling code, even if the method incorporates a line numbered 0.
Take a look at the following code:
Sub on_error_goto_0() Dim copies As Worksheet On Error Resume Next For Each copies In ActiveWorkbook.Sheets copies.Visible = False Next copies On Error GoTo 0 ActiveSheet.Name = "Copy-4" End Sub
We have already shown you this code. This code basically hides all the worksheets in your current workbook. Now, I indeed have an extra piece of code with On Error GoTo 0 to show the error. If you run the code, you will see the following:
It shows this error because we cannot have sheets with the same name in the active workbook.
2. VBA On Error GoTo line
Now, you can also instruct Excel to run another segment of code if it finds any error by using the On Error GoTo line. It tells Excel to execute something after finding an error.
The line argument is any line tag or line number. If our code causes any run-time error, it will move to the line, causing the error handler active in the execution. Remember, your defined line must be in the exact procedure as the On Error statement; otherwise, it will cause a compile error.
Take a look at the following code:
Sub on_error_goto_line() Dim copies As Worksheet On Error Resume Next For Each copies In ActiveWorkbook.Sheets copies.Visible = False Next copies On Error GoTo error_handler ActiveSheet.Name = "Copy-4" Exit Sub error_handler: MsgBox "There is also a sheet with the same name. Try a different one." End Sub
You saw the code in the previous example. When we used On Error GoTo 0, it caused an error. But, here we replaced that with the On Error GoTo line statement.
Now, run the code and you will see the following:
As you can see, it doesn’t show us the standard error dialog box. Instead, it shows the custom message box that we created in the error_handler segment. When Excel finds any error, it jumps to the error_handler segment and shows us the message box.
We also used an Exit Sub in the procedure. If there is no sheet named “VLOOKUP“, our VBA code will rename the active sheet. Then, we have to finish the execution here because we do not need to carry on to the error handler and display the message box.
VBA ‘On Error’ Not Working in Excel
Sometimes, no matter how hard you try, the On Error method won’t work. Previously, we implemented the On Error method to handle the errors. But, sometimes it will show errors even if you use On Error Resume Next or On Error GoTo 0. There might be several reasons in your code that you have to fix. I am not going to show you that.
The basic reason for VBA ‘On Error’ Not Working in Excel is turning on the “Break on All Errors” option in Excel.
Follow these steps to solve that:
- First, press Alt+F11 on your keyboard to open the VBA editor.
- Now, click on Tools > Options.
- After that, click on the General tab in the Options dialog box.
- Here, you can see, “Break on All Errors” is already checked. It basically prevents you from handling the errors.
- To change it, select the option “Break on Unhandled Errors” and click on OK.
I hope it will fix your problem of VBA “On Error” not working in Excel.
💬 Things to Remember
✎ On Error Resume Next doesn’t fix the errors. It basically ignores the error and proceeds to the next statement.
✎ Excel traps and stores the run-time errors in Err object. When we use the On Error Resume Next statement, it clears Err object properties.
✎ You can turn off the On Error Resume Next statement in your VBA code in Excel by adding the On Error GoTo 0 statement.
Download Practice Workbook
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the On Error Resume Next in Excel VBA. I recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!