One of the most important jobs in Microsoft Excel VBA is error handling. If you’re a programmer, you’re well aware of the necessity of error handling in creating a flawless application. Any error in a statement can wreak havoc on your VBA code in a variety of ways. As a result, you must exercise caution when handling errors in the VBA code. Today, In this tutorial, we will learn very quickly and the most time-saving way to VBA On Error Resume Next turn off in Excel with appropriate illustrations.
On Error Statements in VBA
While working with Microsoft Excel VBA, we will face a lot of errors in our sub-procedure. When VBA can not run a statement, it shows a run-time error. 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. The description of these On Error statements is given below.
|On Error GoTo line||Any line label can be used as the line parameter. Control jumps to a line if a run-time error occurs, activating the error handler.|
|On Error Resume Next||While executing a VBA code, if an error happens then it stops executing. When a run-time error occurs, the command is passed to the statement immediately after the one that caused the error, and execution continues.|
|On Error GoTo 0||Turns off any enabled error.|
Introduction to the On Error Resume Next Statement 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. When your code is needed to run even if an error happens, the On Error Resume Next statement allows it. The On Error Resume Next statement can not correct an error, but this statement just ignores the error.
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.
Now, look at the following VBA code.
Sub divide() MsgBox 10 / 0 MsgBox 10 / 5 End Sub
We tried to divide 10 with 0 and 5. 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 VBA 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 10 / 0 MsgBox 10 / 5 End Sub
Hence, run the code, you will be able to 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.
Excel VBA: Turn Off the “On Error Resume Next”
Let’s say, we have a dataset that contains information about several students of Cantonment School and College. The names of the students and their identification number and their securing marks in Physics and Chemistry are given in columns B, C, D, and E respectively. We will turn off the On Error Resume Next statement by applying a VBA code. Our article is about how to turn off the On Error Resume Next. You can solve this problem in one simple way. The way is On Error Goto 0 statement. Here’s an overview of the dataset for today’s task.
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. Follow the instructions below to turn off the On Error Resume Next statement!
Read More: Excel VBA Error Handling in Loop
Step 1: Open the Visual Basic Window
- Firstly, from your Developer ribbon, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2: Run the VBA Code to Turn Off the On Error Resume Next Statement
- Now we will write down the VBA code. Type the VBA Code below in your module.
Sub error_handling() ' Updated by Exceldemy_Rasel(0043) Dim M As Long 'to ignore error On Error Resume Next For M = 5 To 14 Cells(M, 11).Value = WorksheetFunction.VLookup(Cells(M, 10), Range("B:C"), 2, 0) Next M 'to turn off on error resume next On Error GoTo 0 MsgBox i / 0 End Sub
- Will create a sub-procedure named
Dim M As Long
- This code will declare dimension M as long type data.
'to ignore error On Error Resume Next For M = 5 To 14 Cells(M, 11).Value = WorksheetFunction.VLookup(Cells(M, 10), Range("B:C"), 2, 0) Next M
- The VBA code will ignore error in this portion.
'to turn off on error resume next On Error GoTo 0 MsgBox i / 0
- As we can not divide any number by zero, the VBA code will display error in this portion.
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- While running the code, a warning message will appear in front of you, and press Debug to get the error code.
- After completing the above process you will be able to turn off the On Error Resume Next statement that has been given in the below screenshot.
Things to Remember
- While ignoring the known error, we will use the “On Error Resume Next”
- You can turn off the On Error Resume Next statement in your VBA code in Excel by adding the On Error GoTo 0
- You also can turn off the On Error Resume Next statement by applying the On Error GoTo -1 statement.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
I hope all of the suitable methods mentioned above to turn off the On Error Resume Next statement will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.