How to Use IFERROR in VBA (With Examples)

You can use the following basic syntax to use the IFERROR function in VBA to display a specific value in a cell if an error is encountered in an Excel formula:

Sub IfError()
    Dim i As Integer
    
    For i = 2 To 11
    Cells(i, 4).Value = WorksheetFunction.IfError(Cells(i, 3).Value, "Formula Error")
    Next i
End Sub

This particular example checks if each cell in rows 2 through 11 of the third column in the current sheet has an error value.

If an error value is encountered, a value of “Formula Error” is assigned to the corresponding cell in the fourth column, otherwise the numerical value from the third column is assigned to the corresponding value in the fourth column.

The following example shows how to use this syntax in practice.

Example: How to Use IFERROR in VBA

Suppose we have the following dataset in Excel that shows the total revenue and units sold of some product at different stores:

Column C uses a formula to divide Revenue by Units Sold to come up with Revenue per Unit.

However, notice that the formula produces a value of #DIV/0! in some cells where we attempt to divide by zero.

Suppose we would like to create a new column that instead displays “Formula Error” for those cells.

We can create the following macro to do so:

Sub IfError()
    Dim i As Integer
    
    For i = 2 To 11
    Cells(i, 4).Value = WorksheetFunction.IfError(Cells(i, 3).Value, "Formula Error")
    Next i
End Sub

When we run this macro, we receive the following output:

The values in column D either show the results from the formula in column C or they display a value of “Formula Error” if an error value is shown.

Feel free to change “Formula Error” in the IfError method in the code to instead display whatever value you would like when an error is encountered.

Note: You can find the complete documentation for the VBA IfError method here.