WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA On Error Resume Next

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

VBA on Error Resume Next Example 2.4

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

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

VBA on Error Resume Next Example 1

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

Example 1.1

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

VBA on Error Resume Next Example 1.2

If I 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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA on Error Resume Next Example 1.5

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

 Example 1.6

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.

VBA on Error Resume Next Example 1.7

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

Example 1.8

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.

VBA on Error Resume Next Example 2.0.1

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

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 have 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 have got the error message. Let’s look at the second employee name in the table.

 Example 2.5

The second employee name is “Gayathri,” but this name doesn’t not exists in the first table, so the 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 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

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

  • “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 a 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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA On Error Resume Next Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More