Excel VBA: Turn Off the “On Error Resume Next”

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.

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.

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

Sub error_handling()

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

• 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.

Conclusion

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.

Related Articles

• How to Solve Overflow Error in VBA
• Excel VBA Error Handling Best Practice
• [Fixed]: Can’t Find Project or Library Error in Excel

<< Go Back To Errors in Excel | Learn Excel