On Error Resume Next: Handling Error in Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel VBA, error handling is one of the critical tasks. If you are a coder, you know the importance of error handling to build a perfect application. Any blunder in a statement can hamper your VBA code in a lot of ways. So, you must be cautious to handle those errors while executing a VBA code. There are a lot of run-time errors you can face while using the VBA code in Excel. To solve one of them, we use the On Error Resume Next statement.

In this tutorial, you will learn to use the On Error Resume Next statement in Excel VBA. This tutorial will be on point with suitable examples and proper illustrations. So, stay with us.


Error Handling in Excel VBA

While working with Microsoft Excel VBA, you will face a lot of errors in your sub-procedure. When VBA cannot execute a statement, it throws a run-time error.

Excel automatically deals with these errors, so when a run-time error emerges, it displays a default error message like the following:

Error Handling in Excel VBA

Now, you can deal with these variously with some VBA statements. I will discuss them in later sections.


On Error Statements in VBA

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.

  • On Error GoTo line
  • On Error Resume Next
  • On Error GoTo 0

When you find an error, use the On Error command. Because if you don’t utilize an On Error declaration, those run-time errors will be disastrous. It will show an error prompt and stop the execution.

When we use an On Error statement, we turn on an “enabled” error handler. The “active” error handler is an enabled handler that starts the error-handling operation. If a mistake arises while an error handler is involved, the current method’s error handler can’t endure the error. After that, the control returns to the calling procedure.

If the calling process has an enabled error handler, it gets triggered to manage the error. If your calling system’s error handler is correspondingly engaged, control gives back via earlier calling procedures until it finds an enabled but inactive error handler. If it can not find any idle enabled error handler, this means the error is catastrophic at its occurring point.

Each time the error handler gives authority back to a calling procedure, that procedure evolves the existing procedure. Execution restarts in the present procedure at the moment selected by the Resume statement when an error handler handles errors in any procedure.


‘On Error Resume Next’ 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.

The On Error Resume Next statement forces implementation to resume with the command instantly pursuing the line of codes that induced the run-time error. This statement permits an execution to skip even if there is a run-time error. If you think a particular line of code can produce an error, place the error-handling routine there rather than putting it in another location within the procedure. 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.

Remember:

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.

Take a look at the following code:

Sub divide()

MsgBox 5 / 0

MsgBox 5 / 2

End Sub

We tried to divide 5 with 0 and 1. 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:

‘On Error Resume Next’ in VBA

When the VB 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 5 / 0

MsgBox 5 / 2

End Sub

After running the code, you will see the following:

Excel vba ‘On Error Resume Next’ in VBA

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.


In the following sections, I am going to provide you with two examples of the On Error Resume Next statement that you can implement into your Excel worksheet using VBA. I recommend you learn and apply all these to your workbook. It will definitely increase your Excel knowledge.


1. ‘On Error Resume Next’ Statement to Hide Worksheets in Excel VBA

Now, in this example, I will show you a VBA code that will hide all the worksheets of your active workbook.

Take a look at the following screenshot:

Here, we have four worksheets. We will 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 the following run-time error:

Examples of ‘On Error Resume Next’ in VBA

Excel shows this error because you can not hide all the sheets in a workbook. So, you have to ignore the error. To do this, you have to 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:

Examples of ‘On Error Resume Next’ in VBA

In the end, you won’t see any errors after execution. So, our On Error Resume Next statement worked really fine in the VBA code.


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

In this example, I will show an example of the VLOOKUP function in VBA. Now, this VBA code also includes the On Error Resume Next statement.

Take a look at the following screenshot:

Here, you can see some people’s names and their ages. In the adjacent table, we will 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

Now, run the macro. You will see the following error:

Now, this is a run-time error. Why does it happen? Take a look at the dataset again:

VLOOKUP Function with ‘On Error Resume Next’ in VBA

As you can see, there is no data for “Aaron” and “Emma”. That’s why it only executes the VLOOKUP for the first entry. After that, it stops the execution. Now, 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:

VLOOKUP Function with ‘On Error Resume Next’ in VBA

As you can see, by applying the On Error Resume Next command, we ignored the error and found the rest of the persons’ ages. Our VBA code didn’t find any data of Aaron and Emma. That’s why it ignored those values and returned the rest of the values in the Excel worksheet.


Turn Off ‘On Error Resume Next’ with Excel VBA

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.

Generic usage:

Sub error_handling()

To ignore errors

On Error Resume Next

// lines of codes

To turn on Error handling

On Error GoTo 0

//lines of codes

End sub

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. Here added an extra chunk of code to demonstrate you. Our code will ignore the errors while performing the VLOOKUP but it will activate the error handling after the On Error GoTo 0 statement.

Turn Off ‘On Error Resume Next’ with Excel VBA

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


VBA ‘On Error GoTo’ Statement

Previously I discussed handling the errors using the On Error method. Our whole article was all about On Error Resume Next. Now, there are also two types of error handling that I will discuss in the following sections.


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 in them. 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 basically 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

We have already shown you this code. This code basically hides all the worksheets in your current workbook. Now, I indeed 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:

VBA ‘On Error GoTo’

It shows this error because we cannot have sheets with the same name in the active workbook.


2. VBA On Error GoTo line

Now, you can also instruct Excel to run another segment of code if it finds any error by 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 active in the execution. Remember, your defined line must be in the exact procedure as 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

You saw the code in the previous example. When we used On Error GoTo 0, it caused an error. But, here we replaced that with the On Error GoTo line statement.

Now, run the code and you will see the following:

VBA On Error GoTo line

As you can see, it doesn’t show us the standard error dialog box. Instead, it shows the custom message box that we created in the error_handler segment. When Excel finds any 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 sheet named “VLOOKUP“, our VBA code will rename the active sheet. Then, we have to 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

Sometimes, no matter how hard you try, the On Error method won’t work. Previously, we implemented the On Error method to handle the errors. But, sometimes it will show errors even if you use On Error Resume Next or On Error GoTo 0. There might be several reasons in your code that you have to fix. I am not going to show you that.

The basic reason for VBA ‘On Error’ Not Working in Excel is turning on the “Break on All Errors” option in Excel.

Follow these steps to solve that:

📌 Steps

  • First, press Alt+F11 on your keyboard to open the VBA editor.
  • Now, click on Tools > Options.

  • After that, click on the General tab in the Options dialog box.

VBA ‘On Error’ Not Working in Excel

  • Here, you can see, “Break on All Errors” is already checked. It basically prevents you from handling the errors.
  • To change it, select the option “Break on Unhandled Errors” and click on OK.

VBA ‘On Error’ Not Working in Excel

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


💬 Things to Remember

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

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


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the On Error Resume Next in Excel VBA. I recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!


Related Articles


<< Go Back To Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo