VBA IFERROR

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

How to use IFERROR in VBA?

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

For an example take the above data only for demonstration.

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.

Code:

Sub Iferror_Example1()

  Dim i As Integer

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

End Sub

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

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 absolutely 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 worksheet function.

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

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.

Like this using 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 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 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 Examples
• Excel VBA Integer Data Type | Examples
• How to Fix VBA Type Mismatch Run-time Error 13?
• Simple Subroutine Task in VBA
• VBA For Next Loop
• ROUND Formula in Excel – Examples
• VBA MOD Examples
• VBA Worksheet Function List
• VBA Format Examples
• AutoFilter in VBA