Excel tips: how to handles formula errors using IFERROR

Excel has an all-purpose error handler function called IFERROR.

If you create a formula and then realise it needs to handle errors, you need to insert the IFERROR function at the start of your formula.

Let’s review this simple formula.

=A1/B1

If cell B1 contains zero, then the above formula will return the #DIV/0! error.

The IFERROR function can wrap around the above formula to handle a zero value in B1.

=IFERROR(A1/B1,0)

The IFERROR has two arguments, or parts. The first argument is the calculation to perform, and the second is what to return if the calculation returns an error. If the calculation doesn’t return an error, then its result is displayed. If the calculation does return an error, the second argument is displayed.

IFERROR handles nearly all Excel’s formula errors. If A1 has a #VALUE error, the above IFERROR function will still display 0. The IFERROR function can, to some extent, mask or hide errors because it handles all errors the same way.

In older Excel versions, IFERROR handles all errors. In the subscription version of Excel, IFERROR doesn’t handle the #SPILL error, which is related to dynamic arrays.

Inserting the IFERROR function into existing formulas can be time-consuming and repetitive, my two main triggers to consider creating a macro solution.

It is important to keep in mind that macros clear the undo list, which means they cannot be undone. This also means you can’t undo anything you did before you ran the macro. Save your file before using a new macro, and then close the file without saving if a problem occurs. Alternatively, test the macro on a copy of the file.

AddIFERROR macro

Figure 1 has the macro that adds an IFERROR function to an existing formula.

This macro works on formulas that are currently displaying an error. The companion video to this article explains how to install and use this macro. The green text in Figure 1 is explanatory text, also known as “comments”. These explain and describe the code, but do not perform any actions.

The item numbers and explanations that follow refer to the blue and white numbers on the left of Figure 1.

01. Two variables are defined. The c variable will be used to refer to each cell in the selected range. The strFormula variable will be used to capture the text of the existing formula.

02. This macro includes a basic error handler command. This command turns on the error handling. When an error is encountered, the code is directed to the ErrorHandler label at the bottom of the code (item 11). This macro may generate an error if the sheet is protected and cells are locked.

03. This block of code turns off screen updating and stops calculation. These settings speed up the macro in larger files or when working with large ranges. We turn these settings back on in item 9.

04. The TypeName function allows you to identify what the user has selected before the macro is run. If a chart is selected, the subsequent code will generate an error. We can stop the macro at this point and display a warning message shown at item 8.

05. The “For Each c in Selection” is a powerful command. It instructs Excel to loop through every cell in the user’s selected range. This makes the macro flexible, because the user can select different sized ranges to add the IFERROR to.

06. This “If statement” checks the cell to see if the cell has a formula. All Excel formulas start with =. The Left function looks at the first character of the formula to confirm it has the = sign. If the cell doesn’t start with =, then nothing is done to that cell. Any values, dates and text entries are ignored.

07. The strFormula variable captures the formula text without the = sign. The Right function extracts text from the right.

The Len function is short for “length”, and it counts how many characters are in the formula.

By subtracting 1 from the Len result, we omit the = sign from the formula when extracting the text from the right.

We then build the IFERROR formula by having =IFERROR (at the start, followed by the original formula and then ,0) at the end. The & symbol is used to join text together.

08. This command displays a pop-up message box informing the user they need to select a range before running the macro. The user must click OK, otherwise the message box stays on the screen. Note that the underscore character on the end of the first line allows you to wrap a single long line of code over two lines.

09. HandleExit: is a label. A label, which is text followed by a colon, marks a place in the code where you can direct the code to move using the GoTo or Resume commands. This is the section that handles closing the macro. We first return Excel to the standard settings for screen updating and calculation.

10. The c variable is cleared, and the macro is stopped.

11. HandleError: is a label. The actions in this section are only performed if an error is encountered. A pop-up message box is displayed. The user must respond by clicking OK. Then the macro is diverted back to the HandleExit label above to close the macro.

Using the Macro

This is a flexible macro that could be used on any file.

The best place to save this type of general-purpose macro is in a file that is referred to as the Personal Macro Workbook. The file is named PERSONAL.xlsb and is saved in the XLSTART folder on your system. It opens each time you open Excel. When you record a macro, it is one of the saving options.

The online extended version of this article includes a downloadable file containing this macro.

If you are new to macros, the companion video explains how to create the Personal Macro Workbook and how to copy and use this macro.

The companion video and Excel file will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]