VBA On Error Resume Next

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA On Error Resume Next

VBA On Error Resume Next is an error handler statement. If the error occurs while running the code, you can use this statement to resume the next line of code by ignoring the error message instead of showing an error.

Those who write codes regularly in excel VBA know they may get errors even after writing proficient codes, but they want to ignore that error and keep running with the next lines of code. An example of getting an error message is when the VLOOKUP worksheet functionVLOOKUP Worksheet FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more does not find the lookup value from the table array. Therefore, it would not return the #N/A error. Rather, it will throw the error: “Unable to get the VLOOKUP property of the worksheet function class.”

VBA on Error Resume Next Example 2.4

It is very difficult to fix the bug if you do not know why we are getting this error. In VBA, we have a feature called “On Error Resume Next.”

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

What Does On Error Resume Next Do in VBA?

There are certain areas as a coder. First, we know this will surely give an error message, but we need to ignore this error to keep going through the code. So, how to ignore that error is a common doubt everybody has.

We can ignore the error using the VBA On Error Resume Next statement and resume the next line of code.

You can download this VBA On Error Resume Next Excel Template here – VBA On Error Resume Next Excel Template

Example #1

Assume you have many worksheets, and you are hiding some of them as part of the VBA project. For example, below are the worksheets we have in our worksheet.

VBA on Error Resume Next Example 1

We have written codes to hide “Sales” and “Profit” sheets, and below is the code.

Code:

Sub On_Error()

    Worksheets("Sales").Visible = xlVeryHidden
    Worksheets("Profit 2019").Visible = xlVeryHidden
    Worksheets("Profit").Visible = xlVeryHidden

End Sub
Example 1.1

We will start running the code line by line using the F8 key.

VBA on Error Resume Next Example 1.2

If we press the F8 key one more time, it will hide the sheet named “Sales.”

Example 1.3
VBA on Error Resume Next Example 1.4

Now, press the F8 key one more time and see what happens.

VBA on Error Resume Next Example 1.5

We have got a “Subscript out of rangeSubscript Out Of RangeSubscript out of range is an error in VBA that occurs when we attempt to reference something or a variable that does not exist in the code. For example, if we do not have a variable named x but use the msgbox function on x, we will receive a subscript out of range error.read more” error because the current line of code says the below.

Worksheets("Profit 2019").Visible = xlVeryHidden

It is trying to hide the worksheet named “Profit 2019,” but there is no worksheet by the name of “Profit 2019”.

In these cases, if the worksheet doesn’t exist in the workbook, we need to ignore the error and continue to run the code by ignoring the “Subscript out of range” error.

The next line in the code says

Worksheets("Profit").Visible = xlVeryHidden

This worksheet exists in this workbook, so we cannot move to this line of code without ignoring the error.

We need to add the “On Error Resume Next” statement to ignore this error.

Code:

Sub On_Error()

    On Error Resume Next
    Worksheets("Sales").Visible = xlVeryHidden
    Worksheets("Profit 2019").Visible = xlVeryHidden
    Worksheets("Profit").Visible = xlVeryHidden

End Sub
 Example 1.6

As you can see above, we have added the statement at the top of the code before any lines start. Now, run the code and see what happens.

VBA on Error Resume Next Example 1.7

Now, we are in the line given the error previously, press the F8 key, and see what happens.

Example 1.8

We have jumped to the next line of code without showing any error because of the statement we have added at the top, which is the “On Error Resume Next” VBA statement.

Example #2

We will see how to use this statement with one more example. Look at the below data structure for this example.

VBA on Error Resume Next Example 2.0.1

We have two tables above. The first table has “Emp Name” and their salary details in the second table. Unfortunately, we have only “Emp Name.” So by using VLOOKUP, we need to fetch the salary details from the left side table.

Below is the code we had written to fetch the details.

Code:

Sub On_Error1()

  Dim k As Long

  For k = 2 To 8
   Cells(k, 6).Value = WorksheetFunction.VLookup(Cells(k, 5), Range("A:B"), 2, 0)
  Next k

End Sub
Example 2.1

Now, run the code line by line and see what happens.

VBA on Error Resume Next Example 2.2
Example 2.3

Upon running the first cell code, we got the result for the first employee. Repeat the same for the second employee as well.

VBA on Error Resume Next Example 2.4

This time we got the error message. Let us look at the second employee’s name on the table.

 Example 2.5

The second employee’s name is “Gayathri,” but this name does not exist in the first table, so the VBA VLOOKUP functionVBA VLOOKUP FunctionThe functionality of VLOOKUP in VBA is similar to that of VLOOKUP in a worksheet, and the method of using VLOOKUP in VBA is through an application. Method WorksheetFunctionread more does not return a #N/A error when the VLOOKUP does not find the lookup value from the table. Rather, it gives the above error message.

Our aim is if the employee name is unfound in the table, then we need an empty cell for that employee, ignore the error, and give results for the remaining names.

We need to add the “On Error Resume Next” statement inside the loop.

Code:

Sub On_Error1()

  Dim k As Long

  For k = 2 To 8
   On Error Resume Next
   Cells(k, 6).Value = WorksheetFunction.VLookup(Cells(k, 5), Range("A:B"), 2, 0)
  Next k

End Sub
VBA on Error Resume Next Example 2.6

Now, run the code and see the result.

 Example 2.7

The two employee names: “Gayathri” and “Karanveer,” are not on the list. So, those line codes must have encountered an error since we have added an error handler statement of “On Error Resume Next.” It has ignored that line of code and resumed for the next employee.

Things to Remember here

  • The “On Error Resume Next” is the error handler statement when we need to ignore the known error.
  • If we want to ignore the error message only for a specific code set, close the On Error Resume Next statement by adding the “On Error GoTo 0” statement.

This article has been a guide to VBA On Error Resume Next. Here, we discuss how to ignore errors and resume the next line of code in Excel VBA with examples and a downloadable Excel template. You can learn more about VBA functions from the following articles: –