WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA IFERROR

Just like we use IFERROR in excel to know what to do when an error is encountered before every function we have an inbuilt IFERROR function in VBA which is used in the same fashion since it is a worksheet function we use this function with the worksheet.function method in VBA and then we provide the arguments for the function.

IFERROR Function in VBA

It is a crime to expect the code to function without throwing any error. To handle errors in VBA, we have several ways using statements like On Error Resume Next VBA, On Error Resume Goto 0, On Error GoTo Label. VBA error handlers can only proceed further to the next line of code. But in case if the calculation doesn’t happen, we need to replace the error with some other identity word. In this article, we will see how to achieve this by using VBA IFERROR Function in excel.

Excel VBA IFERROR

How to use IFERROR in VBA?

The thing to remember here is it is not a VBA function rather just as a worksheet function.

You can download this VBA IFERROR Excel Template here – VBA IFERROR Excel Template

For example, take the above data only for a demonstration.

VBA IFERROR Example 2

Step 1: Define the variable as an integer.

Code:

Sub Iferror_Example1()

  Dim i As Integer

End Sub

Step 2: To perform calculation, open For Next Loop.

Code:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6

  Next i

End Sub

Step 3: Inside the write the code as Cells(I,3).Value =

Code:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
     Cells(i,3).Value =
  Next i

End Sub

Step 4: To access the IFERROR function, we cannot simply type the formula; rather, we need to use the “WorksheetFunction” class.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 3).Value = WorksheetFunction.If
  Next i

End Sub

VBA IFERROR Example 2-1

Step 5: As you can see in the above image, after inserting the command “WorksheetFunction” class, we get the IFERROR formula. Select the formula.

Code:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 3).Value = WorksheetFunction.IfError(
  Next i

End Sub

Example 2-2

Step 6: One of the problems in VBA while accessing the worksheet functions we don’t get to see the arguments like how we have seen in the worksheet. You need to be sure about the arguments we are using.

This is the reason before I show you the IFERROR in VBA, I have shown you the syntax of the worksheet function.

The first argument here is “Value,” i.e., what is the cell you want to check? Before this, apply the calculation in Cell.

Example 2-3

Now in the VBA, apply the codes below.

Code:

Sub Iferror_Example1()

  Dim i As Integer

  For i = 2 To 6
    Cells(i, 4).Value = WorksheetFunction.IfError(Cells(i, 3).Value, "Not Found")
  Next i

End Sub

Now IFERROR function checks for any error in column C. If any error finds it will show the result as “Not Found” in column D.

Example 2-4

Like this using the IFERROR function, we can alter the results as per our wish. In this case, I have altered the result as “Not Found.” You can change this to your requirement.

Types of Errors, VBA IFERROR, Can Find

It is important to know the types of excel errors the IFERROR function can handle. Below are the kind of errors IFERROR can handle.

#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Recommended Articles

This has been a guide to VBA IFERROR Function. Here we learned how to use the VBA IFERROR function in Excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Type Statement
  • VBA Type Mismatch Error
  • What is OR Function in VBA?
  • Excel VLOOKUP Errors
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ 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 IFERROR Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More