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.

Statement	Description
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	If an error occurs the code 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

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.

Note:

The On Error Resume Next statement doesn’t fix runtime errors, it bypasses them.

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:

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

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

Click Debug,

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

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.

Run the VBA Code to Turn Off the On Error Resume Next 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.

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

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

Explanation:

Sub error_handling()

Creates a sub-procedure named

Dim M As Long

Declares 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

Ignores errors in this portion.

'to turn off on error resume next
On Error GoTo 0
MsgBox i / 0

As we cannot divide a number by zero, the VBA code will display an error in this portion.

Press F5 to run the code, or:

Run → Run Sub/UserForm

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

A warning message appears.

Press Debug to get the error code.

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

After completing the above process you will be able to turn off the On Error Resume Next statement as in the below screenshot.

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

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

Turn Off On Error Resume Next.xlsm