On Error Resume Next: Handling Error in Excel VBA

Method 1 – On Error Resume Next’ Statement to Hide Worksheets in Excel VBA

This is a VBA code that will hide all the worksheets of your active workbook.

Look at the following screenshot:

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 this run-time error:

Excel shows this error because you can’t hide all the sheets in a workbook. Ignore this error. You must 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:

After execution, you won’t see any errors. This On Error Resume Next statement worked fine in the VBA code.

Method 2 – Applying Excel VLOOKUP Function with ‘On Error Resume Next’ in VBA

This is an example of the VLOOKUP function in VBA. This VBA code also includes the On Error Resume Next statement.

Take a look at the following screenshot:

You can see some people’s names and ages. In the adjacent table, 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

Run the macro. You will see the following error:

This is a run-time error.

There is no data for “Aaron” and “Emma”. That’s why it only executes the VLOOKUP for the first entry. It stops the execution. 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:

By applying the On Error Resume Next command, we ignored the error and found the rest of the persons’ ages. The VBA code didn’t find any data on Aaron and Emma, so it ignored those values and returned the rest in the Excel worksheet.

Turn Off ‘On Error Resume Next’ with Excel VBA

You may be in a situation where you want to ignore errors for a particular segment of the VBA code. 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.

Generic usage:

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. Our code will ignore errors while performing the VLOOKUP but will activate error handling after the On Error GoTo 0 statement.

VBA ‘On Error GoTo’ Statement

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

This code hides all the worksheets in your current workbook. We 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:

This error is caused by the fact that sheets with the same name cannot be in the active workbook.

Method 2 – VBA On Error GoTo line

You can also instruct Excel to run another code segment if it finds any error 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 to be active in the execution. Your defined line must follow the exact procedure for 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

We used On Error GoTo 0, which caused an error. We replaced that with the On Error GoTo line statement.

Run the code, and you will see the following:

It doesn’t show the standard error dialog box. Instead, it shows the custom message box we created in the error_handler segment. When Excel finds an 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 ” VLOOKUP ” sheet, our VBA code will rename the active sheet. We must 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

Steps

• Press Alt+F11 on your keyboard to open the VBA editor.
• Click on Tools > Options.

• Click on the General tab in the Options dialog box.

• When “Break on All Errors” is checked, it prevents you from handling the errors.
• To change it, select “Break on Unhandled Errors” and click OK.

I hope it will fix your problem of VBA “On Error” not working in Excel.

Things to Remember

✎ On Error Resume Next, it doesn’t fix the errors. It ignores the error and proceeds to the next statement.

✎ Excel traps and stores the run-time errors in the 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

Related Articles

• How to Solve Overflow Error in VBA
• Excel VBA Error Handling in Loop

<< Go Back To Errors in Excel | Learn Excel