INTHEBLACK December 2021 - Magazine - Page 73
CLICK HERE TO ACCESS
extra material, a podcast
and a video from
Neale Blackwood CPA
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
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.
This is a flexible macro that could be used on
any file.
The best place to save this type of generalpurpose 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.
intheblack.com December 2021 73