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

In this tutorial, we will demonstrate the most effective way to turn off On Error Resume Next in Excel VBA.

The On Error Statement in VBA

When VBA cannot run a statement, it shows a run-time error. We handle run time errors with the On Error statement, which sets what to do after the error. We are essentially disabling these errors with this method of error handling.

There are three kinds of On Error statements (syntax) in VBA.

Introduction to the On Error Resume Next Statement in Excel VBA

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. When you need your code to run even if an error happens, the On Error Resume Next statement allows it.

The On Error Resume Next statement becomes idle when your code calls another procedure, so if you need error handling in that routine, you have to run another On Error Resume Next command in each named pattern. This is a reasonable approach when the line of code being skipped is not critical to the running of the macro. But used incorrectly, it may provide unintended results.

Consider the following VBA code:

Sub divide()
MsgBox 10 / 0
MsgBox 10 / 5
End Sub

We are trying to divide 10 by 0 and then by 5.

When we run run the code, it returns the following output:

The run-time error is because we can’t divide a number by 0.

Click Debug,

When the VBA program finds an error, it immediately stops the procedure and 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

Run the code. Now the result looks like this:

VBA ignores the line that produces the error and proceeds immediately to the following line of code.

Turn Off “On Error Resume Next”

Suppose we have the dataset below containing information about several students. Let’s turn off the On Error Resume Next statement by applying the On Error Goto 0 statement.

Suppose we only want to ignore errors for a particular segment of the VBA code. After using the On Error Resume Next statement in a VBA code, all the errors after that line will be skipped. To turn that off and re-enable the error handling for a later segment of code, we use On Error GoTo 0.

Read More: Excel VBA Error Handling in Loop

Step 1 – Open the Visual Basic Window

• From the Developer tab, click on Visual Basic.

• In the Microsoft Visual Basic for Applications window that appears, click on Insert → Module.

Step 2 – Run the VBA Code to Turn Off the On Error Resume Next Statement

• In the Module window that opens, enter the following code:

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

• Press F5 to run the code, or:

Run → Run Sub/UserForm

A warning message appears.

• 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 as in the below screenshot.

Things to Remember

• To ignore a known error, use the “On Error Resume Next”
• Turn off the On Error Resume Next statement by adding the On Error GoTo 0
• Alternatively, turn off the On Error Resume Next statement by applying the On Error GoTo -1 statement.

Download Practice Workbook

Related Articles

• How to Solve Overflow Error in VBA

<< Go Back To Errors in Excel | Learn Excel