|Key to Instructions
To help readers follow the
instructions in this article, we used two
is used to identify the names of
icons, agendas and URLs.
Sans serif type shows
commands and instructions users should
type into the computer and the names of
acros, those do-it-yourself software
programs, rank among Microsoft’s most useful
tools. They automate many computer tasks that you
otherwise would have to execute manually—from the
simple task of creating customized worksheets to
the very complex tasks of exporting journal
entries in Excel into an accounting package and
creating reports in Word.
macros so wonderful is that in many cases you
don’t have to be an expert to set them up. If
you’re willing to invest a little time to learn
the language they are written in, Visual Basic
(VB), you can make them perform some astonishingly
complicated jobs—such as handling an entire
monthly close. For this article we will focus on
macro basics that do not require you to program in
VB. But be forewarned, once you see how powerful
macros are, you may find yourself anxious to learn
Although macros are
available in the entire Microsoft Office
Suite—including Word, Access, PowerPoint and
Outlook—we’re going to show you how they work in
Excel, which is where they really show their
muscle for CPAs and other finance professionals.
Once you start using them, you’ll likely find your
work output increasing substantially.
Follow along and we’ll create a macro simply by
recording the keyboard strokes and mouse clicks
needed to perform a typical accounting
task—setting up a workpaper. As you proceed, Excel
translates your recorded steps into VB. Once
they’re recorded you can command Excel to replay
them. It usually takes about five minutes to set
up workpapers manually; with a macro, it takes
As you can see in exhibit 1
, below, we have a standard custom format for
all our workpapers, which includes a line for the
client name, workpaper name, period, purpose,
initials and date.
Excel to begin recording, select Tools,
Macros, Record New Macro ( exhibit
That will engage
the Record Macro dialog box
(exhibit 3 ).
name select something short and
friendly. Note that macro names must be one word.
We’ve selected SetupWorkpaper . Under the
Shortcut key pick a letter
that, when pressed simultaneously with Ctrl, will
execute the completed macro. We’ve selected the
letter S . The shortcut key method is
only one of the numerous ways to execute the
macro; we’ll show you more later.
Store macro in you have several
location options. If the macro will be run in only
one specific workbook, save it in that workbook.
If you would like to run the macro in several
workbooks, save it in your Personal Macro
Workbook. It’s important to remember
where you save the macro text.
If you save
it in the Personal Excel Workbook
, a dialog box asking you to confirm your
decision will open ( exhibit 4 , below).
Click on Yes .
Now you’re ready
to begin the recording process. Click on
OK in exhibit 3 , which
remains on your screen. Excel signals that it’s
ready to record your keystrokes by the presence of
the small Stop dialog box (
exhibit 5 ).
Now perform all
the steps to set up your custom workpaper, such as
entering the text and formatting it. When you’re
done, click on the Stop button.
EXECUTING A MACRO
Once you have a macro
in memory, let’s see how to run it. Begin by
opening a new workbook. Remember we mentioned
there are several ways to launch your macro. You
can use the shortcut key, Ctrl+S, which is the
easiest; however, if you create many macros you
may not be able to remember which key triggers
which macro. The other methods—the Form
button, the Toolbar
button or the Macro
box—provide you with the macro name.
The Form button:
Click on View, Toolbars and
Forms ( exhibit 6 ,
Doing that will
launch the Forms Toolbar
(exhibit 7 , below).
Click on the
button icon (row 2, right side) and then click
anywhere in your worksheet to create the button,
as shown in exhibit 8 , below.
When you click
on the button, you will be prompted by the
Assign Macro screen to identify
which macro to run. Each macro that you create
will be listed under Macro name .
The Toolbar button:
To create such a button, click on Tools
, Customize and then on
the Toolbars tab ( exhibit 9
Macros from the
Commands field and click on and
drag Custom Button to anywhere in
the toolbar. Later, by right-clicking on the newly
created toolbar button, you can change the smiley
face icon and then assign the macro ( exhibit
10 , above).
The Macro dialog
box: Select Tools and
Macro ( exhibit 11 ,
That will launch
the Macro dialog box, which then
will list all the available macros ( exhibit
12 , above).
To execute a macro,
select it from the list and click on Run
LOOK AT THE CODE
For those who would
like to “look under the hood” and see the VB code
that Excel wrote for the macro, click on
Tools , Macros
and Visual Basic Editor
. Here you can view the code, and once
you become familiar with the language, you can
edit it, too. The box below shows what a portion
of the code looks like.
recorded 5/20/2004 by Jeff Lenning
' Keyboard Shortcut: Ctrl+s
ActiveCell.FormulaR1C1 = "Click
"Accounts Receivable Detail"
ActiveCell.FormulaR1C1 = "FYE:
ActiveCell.FormulaR1C1 = "The
purpose of this worksheet is to "
ActiveCell.FormulaR1C1 = _
"The purpose of this worksheet
is to provide the detail"
ActiveCell.FormulaR1C1 = _
"The purpose of this worksheet
is to provide the detail for the "
"6/30/2004 Accounts Receivable
Selection.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.AddIndent = False
.IndentLevel = 0
.ReadingOrder = xlContext
.MergeCells = False
If you want to
learn more about VB, check out Microsoft’s Visual
Basic for Applications home page at
http://msdn.microsoft.com/vba/ . There also
are many books on the subject; one popular one is
Mastering Visual Basic 6 by Evangelos
Petroutsos (Sybex Inc., 1999).
don’t want to spend the time writing VB code from
scratch, there are many places on the Internet
where you can find completed macros for many
different applications that you can adapt for your
own use. Just do a Google search for Visual Basic
Whether you become an expert in
macro code or just use the recording method,
you’ll find macros can help you speed many of your
repetitive operations and boost your productivity.
JEFF LENNING, CPA, is the
founder of Click Consulting, Seal Beach,
California. His company provides IT solutions,
support and development. His e-mail address is
email@example.com and his Web site is