Table of Contents
IFERROR is a VBA function that is used to catch errors and return a custom value instead. It is often used in conjunction with other functions, such as ISERROR, to ensure that errors are handled appropriately. IFERROR takes two arguments: a value and a value_if_error. If the value produces an error, the value_if_error will be returned instead. IFERROR is a useful tool for controlling how the code handles errors and can help prevent your code from crashing.
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.