Excel VBA On Error Resume Next
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.”
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.”
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.
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 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.
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.
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 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.
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.
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.
Recommended Articles
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: –
- VBA Split String into ArrayVBA Square RootType Mismatch Error in VBA1004 Error in VBA
