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 function 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."

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

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.

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.

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

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

If we 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 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

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.

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

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.

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

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

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

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

The second employee's name is "Gayathri," but this name does not exist in the first table, so the VBA VLOOKUP function 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

Now, run the code and see the result.

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.

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