VBA On Error Resume Next

Excel VBA On Error Resume Next

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

Those who write codes regularly in excel VBA know that 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. One typical example of getting an error message is when the VLOOKUP worksheet function doesn’t find the lookup value from the table array it won’t return #N/A error rather it will throw the error as “Unable to get the VLOOKUP property of the worksheet function class”.

If you don’t know why we are getting this error then it is very difficult to fix the bug. In VBA we have a feature called “On Error Resume Next”.

What does On Error Resume Next Do in VBA?

There are certain areas as a coder we will be knowing for sure this will give an error message but we need to ignore this error to keep going through the code, so how to ignore that error is the common doubt everybody has.

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

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 I have in my worksheet.

I have written codes to hide “Sales & “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

Now I will start running the code line by line using the F8 key.

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

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

We have got a “Subscript out of range” 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 does exist in this workbook so without ignoring the error we cannot move to this line of code.

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

Code:

Sub On_Error()

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

End Sub

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

Now I am in the line which was given the error previously press F8 key and see what happens.

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

Example #2

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

We have two tables above the first table that have Emp Name and their salary details in the second table 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 I 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

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

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

This time we have got the error message, let’s look at the second employee name in the table.

The second employee name is “Gayathri” but this name doesn’t not exists in the first table so VBA VLOOKUP function doesn’t return “#N/A” error when the VLOOKUP doesn’t find the lookup value from the table rather it gives the above error message.

Our aim is if the employee name doesn’t find in the table then we need an empty cell for that employee and ignore the error and give results for 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

Now run the code and see the result.

Two employee names “Gayathri & Karanveer” are not there in 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 next employee.

Things to Remember here

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

Recommended Articles

This 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 downloadable excel template. You can learn more about VBA functions from the following articles –

• VBA Split String into Array
• VBA Square Root
• Type Mismatch Error in VBA
• 1004 Error in VBA