This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Latest Stories

Latest Stories

Automate Excel Functions

Easy-to-create macros can take over many manual processes.

BY JEFF LENNING

Related

TOPICS

Uncategorized Article

Key to Instructions
To help readers follow the
instructions in this article, we used two
different typefaces: Boldface type
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
files.

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.

What makes
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
the language.

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
seconds.

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.

Under Macro
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.

Under
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.

Select
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).

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.

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).

If you
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
macros.

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.

Macro Security
Depending on which version of Excel
you have and whether you’ve downloaded the latest
Microsoft service pack, you may get an alert each
time you open a file that contains a macro.
Microsoft inserted the alert because macros can be
programmed to act like viruses—instructed to delete
files and otherwise wreak havoc on your system.

It’s a good security practice to never enable
macros unless you are absolutely sure they come
from a trusted source.

There are over 30 million small businesses in the U.S., and many of them are optimistic in their outlook. Are you familiar with the obstacles and opportunities they are facing? Test your small business acumen with this quiz sponsored by Chase Ink®.