INTHEBLACK December 2021 - Magazine - Page 72
WORK SMART
// E XC E L YO U R S E L F
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
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.
ADD IFERROR MACRO
STORY NEALE BLACKWOOD CPA
MACRO TO AMEND
FORMULA ERRORS
THE IFERROR FUNCTION HANDLES MOST FORMULA ERRORS IN EXCEL.
E
xcel 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)
72 ITB December 2021
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.
Figure 01 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 01 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 01.
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.