FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA IFERROR

Updated on June 7, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Just like we use IFERROR in Excel to know what to do when we encounter an error before every function, we have an inbuilt IFERROR function in VBA 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 if the calculation does not happen, we need to replace the error with another identity word. In this article, we will see how to achieve this by using the VBA IFERROR Function in excel.

VBA IFERROR

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA IFERROR (wallstreetmojo.com)

How to use IFERROR in VBA?

The thing to remember here is it is not a VBA function but rather just 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, 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; we need to use the “WorksheetFunction” class.

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 is that while accessing the worksheet functions, we do not get to see the arguments we have seen in the worksheet. It would help if you were sure about the arguments we are using.

It is the reason before we show you the IFERROR in VBA, We have shown you the syntax of the worksheet function.

The first argument is “Value,” i.e., what cell do you want to check? Before this, apply the calculation in the 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, the IFERROR function checks for any error in column C. If it finds any error, it will show the result as “Not Found” in column D.

Example 2-4

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

Types of Errors, VBA IFERROR, Can Find

Knowing the types of excel errors the IFERROR function can handle is important. Below are the kind of errors IFERROR can handle.

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

Recommended Articles

This article is a guide to VBA IFERROR Function. Here, we learned how to use the VBA IFERROR function in Excel, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –