Yes, You Can Program in VBA

Writing macro code is easier than you think.

IF THE THOUGHT of having to
perform software programming leaves you anxious,
then read this article and you’ll discover it’s
not as difficult as it first appears.

ONCE YOU LEARN HOW to read and
write the code in Visual Basic, a new world of
software tools will open to you, enhancing your
productivity mightily.

IF YOU’VE CREATED MACROS in
Excel or Word, you’ve probably done some basic
programming already. It’s easy to step up and
edit those macros to perform more sophisticated
automated tasks.

VISUAL BASIC IS A programming
language supported by many of today’s most
popular business applications, including all the
applications in the Microsoft Office Suite. All
that’s required to start programming is to turn
on the built-in VBA Macro Recorder.

WHEN RECORDING A MACRO, the
application actually writes much of the code
itself.

THIS ARTICLE WALKS you through
the creation of a macro that, with a few
keystrokes, automatically produces distinctive
column headings.

JUSTIN D. STEIN, CPA, is a manager in
the tax technology consulting group at Arthur
Andersen, LLP, in New York. His e-mail address is justin.d.stein@bigfoot.com
.

f the thought of having to perform some
software programming leaves you anxious, then you must read
this article. You’ll find—much to your surprise and
delight—that if you’ve created macros in Excel or Word,
you’ve probably done some basic programming already. You’ll
also find that you can step up easily and edit those macros
in Visual Basic for Applications (VBA) to perform more
sophisticated automated tasks.

The code looks
daunting but you’ll soon see that it’s quite intuitive. Once
you learn how to read and write the code, a new world of
software tools will open to you, enhancing your productivity
mightily. This article introduces you to simple macro
writing and then shows you how to edit the code to fine-tune
and customize the program.

VBA is a programming
language that is supported by many popular business
applications, including the apps in the Microsoft Office
Suite. All that’s required to start programming is to turn
on the VBA Macro Recorder, which is built into all the apps,
perform the tasks you want the macro to do, and voila!
You’ve created Visual Basic code for a task that can be
repeated whenever you wish, saving you loads of time.

For this tutorial, we’ll create special Excel column
headings. As you know, Excel column headings don’t contain
visual cues to help users differentiate headings from data.
So we’ll create a routine that produces a distinctive column
heading. Once we’ve created the macros, they can be used for
other Excel worksheets as well. The column heading will have
a navy blue background and a bold white font ( exhibit
1 ).

To begin, select all the cells in the
range to be formatted. Now go to the toolbar and click on
Tools, Macro and Record New
Macro, which will evoke the Record Macro
dialog box ( exhibit
2 )

Select a macro name that’s descriptive,
such as: FormatColumnTitle. Leave no spaces between the
words—a quirk of the Microsoft language. If you want to be
able to run this macro using a combination of keys, move
your cursor to the Shortcut key field and
select a key combination. If you want to use Ctrl+H to run
the macro, enter H in the Ctrl+ box; if you want to use
Ctrl+Shift+H, press the Shift and H keys simultaneously; the
Shortcut key field automatically updates
with your selection. For our example, let’s assign
Ctrl+Shift+H.

Caution. When assigning shortcut keys, be careful
you don’t replace one of the standard Excel shortcuts, such
as Copy (Ctrl+C), Paste (Ctrl+V), Cut (Ctrl+X), Undo
(Ctrl+Z), Save (Ctrl+S), Open (Ctrl+O) or Print (Ctrl+P).

Next, decide where to store the macro. If the macro
will be accessed only from the current file, save it to
This Workbook. If you want to access the macro
from any workbook, click on the drop-down arrow at the
Store macro in field and select
Personal Macro Workbook; that’s a special file
named “Personal.xls” which automatically loads each time
Excel starts, making it available in any spreadsheet. Let’s
store the macro in the Personal Macro Workbook and
type a description of what we expect this macro to do and
then click OK ( exhibit
3 ).

Notice that the status bar at the lower
left portion of your screen now reads “Recording” ( exhibit
4 ). That tells you the macro recorder is on, storing
each keystroke and mouse action. So be careful that you
perform only those steps necessary to format the columns,
because every step is saved and will be repeated each time
you run the macro.

Now we’re ready to tell Excel
what we want this routine to do. Right-click on any cell in
the selected range and choose Format Cells.
In the Alignment tab, change
Horizontal to Center Across
Selection and check the box for Wrap text
( exhibit
5 ). In the Font tab, change
Font style to Bold and
Color to White. In the Border
tab, choose the solid line style, change the
Color to white, then click
Outline and Inside .

Finally, select the Patterns tab and
select navy (exhibit
6). Click OK when finished and you will see your
spreadsheet update with the formatting you just applied.

Remember that the macro
recorder is still operating, indicated by the Stop
Recording toolbar floating over your workbook. If
it’s not there, right-click on any toolbar, choose
Stop Recording and then click on the blue
square.

To see how the macro worked out, open the
Visual Basic Editor (VBE) by pressing Alt+F11—the shortcut
to clicking on Tools, Macro, Visual Basic Editor
—and you’ll see a screen that resembles exhibit
7.

The Project window (the
upper right section) contains two projects: Personal.xls and
Spreadsheet Smarts Examples.xls, representing the open Excel
workbooks. Below that is a Properties
window that describes the selected object, which in
this case is the worksheet shown in exhibit
1 . Since we told Excel to store the FormatColumnTitle
macro in the Personal Macro Workbook, expand the
Personal.xls file by clicking on the plus sign (+) to the
left of that project. That will open two folders,
Objects and Modules .
Click on these folders and you will see all of the sheets in
the Personal Macro Workbook in addition to a code module
named Module1. (If you created any other macros and saved
them to your Personal Macro Workbook, you may have more than
one module. If this is the case, open the last module to
find the routine you just created.) Double-click on Module1
to open it and view its contents. You should now see many
indented lines with blue, black and green words that
represent the VBA code that Excel wrote when you created the
macro.

Notice that even though we didn’t set the
font style while recording the macros, Excel still wrote the
code: . Fontstyle = “Bold” and .Size = 10. Excel is
programmed to record all properties of all fields within a
dialog box even though you changed only one item during the
recording session. Thus our code contains properties that
are irrelevant to our desired task. Therefore, remove
specific lines of code that you don’t want your routine to
perform the next time. To do that, place your cursor at the
left edge of the VBE window so that the cursor points to the
right. Position your cursor on the line where we set the
font to Arial, then click to select the entire line.

Press Delete and the line will disappear. We removed that
code because you may use different fonts in your documents
and we don’t want to override them within this macro. Our
next step is to remove all remaining lines describing the
font so that only .FontStyle and . ColorIndex are left.

How VBA
Works

VBA is what’s called an
object-oriented programming language. That means
the code is made up of programming objects,
which are characterized in two ways: by their
properties and methods. Properties are descriptive
characteristics of the object. Methods are actions
performed by the object. For example, cars have
properties such as EngineType, Color,
NumberOfDoors and ModelYear. Methods include
GoForward, GoBackward, Honk and Stop.

Objects also can contain other objects. For
example, cars also have doors, which have
properties and methods of their own. Doors can
have a Color property and an Open method. If we
want to say the car’s left front door is red, type
Car.FrontLeftDoor.Color = Red.

You can probably intuitively gather
the meaning of the code in exhibit
8. The first line, With
Selection.Font means “the
following list describes the font of the selected
range.” The With statement tells the user that
whatever appears on that line will be used as a
prefix for each line in the list below. For our
example, the next line, .Name = “Arial,”
tells us that our font is Arial.

This macro will change
the format of any cells you select to indicate a column
heading. However, if you want to apply this format to a
single cell, rather than to a range of cells, you’ll have to
insert a couple of additional lines. Add If
Selection.Cells.Count > 1 Then and End If around the code
section beginning with With
Selection.Borders(xlInsideVertical) to permit a specific
section of code to run only if more than one cell is
selected. The finished code is shown in exhibit
9 .

What if you don’t like using
the assigned keyboard strokes (CtrlL+Shift+H) to evoke the
macro but prefer to use toolbar buttons instead? For that
option, click on Tools , choose
Customize and then select the
Commands tab. Scroll down the list and
choose Macros and you will see the
selections change in the adjacent Commands
column. Click on the Custom button
and drag it to a toolbar. Right-click on the
button, type Column Heading in the Name
field, then click EditButton Image
to change the image to something a bit more
descriptive, perhaps text or an icon representing the
appearance of the final format of the cell: .

Finally, click
Assign macro, choose “Personal.
xls!FormatColumnTitle,” click OK and
Close . You now have an icon that can
access the same routine as your keystrokes.

Now that
you know the fundamentals of writing macros in VBA, you’re
sure to come up with plenty of applications that you can
launch with just a few keystrokes. A small investment of
energy now can save you loads of time later.

An
Invitation

If you have a special
how-to technology topic you would like the
JofA to consider for inclusion in this
series, or an application shortcut you devised and
would like to share with other professionals,
contact Senior Editor Stanley Zarowin. His e-mail
address is zarowin@mindspring.com
.

When professionals prepare written material for readers inside their organization or outside, they should make sure that no errors distract from the message they need to convey. Take this short quiz for practice in subject-verb agreement.