<<

. 50
( 62 .)



>>

automated sequences that you can launch whenever you want,
so that you do not have to keep typing in the keystrokes
yourself. Macros can be used to replicate any command
sequences, and they use Excel™s VBA programming language.
At higher levels of programming, they can be made to operate
other levels of Excel and interact with outside sources of data, for
example. But don™t worry, we will not go off that deep end in
this chapter.
You can create a macro by writing the code for it, or you can
have Excel automatically write the macro for you by setting it in
a mode to record your keystrokes. We will get a little more
deeply into macros in Chapter 19, but, even so, we will only
be dipping our toes into the subject, as VBA is a subject that
can fill volumes in its own right.
We will examine the steps for
Recording a macro
u

Running it again
u

Looking at the macro itself
u


271



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 16
272




WHEN TO USE A RECORDED MACRO
A recorded macro gives a quick no-fuss way of getting some auto-
mation into your worksheet. You should use it when the model is
fairly simple and is static: All the rows are fixed and you know the
exact locations of the data points you want to work with, whether
in copying and pasting, changing fonts or formats, or printing. If
this is so, with the addition of on-screen controls (Chapter 17), you
can get your basic model to look and function with more flourish.


RECORDING A MACRO
Let™s start with a new file so that we can experiment to our
hearts™ content without jeopardizing anything that we have done.


Turn on the Recording Mode
Go to Sheet 1 first. Then do the sequence Tools > Macro > Record
New Macro. You will see the dialog box shown as Figure 16-1
pop up in the middle of your screen.
You can leave everything as it is and just click on OK to
begin recording your macro, but I want to point out something
about the ˜˜Shortcut key™™ field.


FIGURE 16“1




TLFeBOOK
Recording Macros 273




The ˜˜Shortcut key™™ entry is something that will enable you
to launch this macro again just by pressing the Ctrl key and the
key you specify here. The key that you specify can be lower
case or upper case. You will see that if you enter in upper
case letter, the sign will read Ctrl þ Shift, and, in fact, you will
have to press the additional Shift key to get to the upper case
character.
Whenever you specify shortcut keys, be careful that you do
not specify a letter that is already used by Excel. For example, the
following keys are used by Excel in combination with the Ctrl
key. (I have used the lowercase letter to indicate that you do not
need to apply the Shift key.)

a To select everything in the sheet; equivalent to clicking on the corner
where the column letter A and the row number 1 meet

b Apply bold type

c Copy

f Find

h Find and replace

i Apply italic type

n Insert new worksheet

o Open ¬le

p Print

s Save

u Apply underline format

v Paste

x Cut

y Redo

z Undo


If you do use one of these letters for a shortcut key, the
native command in Excel will be overridden by your shortcut
setting. A good approach you can use to avoid this kind of
mix-up is to use the Ctrl þ Shift combination, since none of
the shortcuts in Excel uses this combination.




TLFeBOOK
Chapter 16
274




FIGURE 16“2




Let™s put the combination Ctrl þ Shift þ T in the dialog box
shown as Figure 16-2. This will allow us to run this macro again
just by pressing this combination. You can set (and reset) this
shortcut key combination later on, by the way.

Enter These Steps
Click on OK. Now the recording mode is on. Let™s do the
following:
Go to cell C1 and enter 10.
1.
Go to cell C2 and enter 20.
2.
Go to cell C3 and enter 30.
3.
Go to cell C5 and enter ¼ SUM(C1 : C3).
4.
Copy the range C1:C5 to Sheet2, cell D1.
5.
Go to Sheet2 and apply the Bold and Italic formats to
6.
cell D5.
7. Go to Cell A1.


Stop the Macro Recording
Stop the macro recording by going to Tools > Macro > Stop
Recording.




TLFeBOOK
Recording Macros 275




Let™s Check What We Did
Did we record the macro properly? Let™s check by deleting all
the entries in Sheet1 and Sheet2. Go back to Sheet1 and put the
cursor anywhere. Now press the shortcut key combination
`
Ctrl þ Shift þ T. Voila! The two sets of numbers we recorded are
run again, appearing once more.



RUNNING THE MACRO WITHOUT
A SHORTCUT KEY
If we had not specified the shortcut key, running the macro again
would require the following sequence: Tools > Macro > Macros.
You will then see the dialog box in Figure 16-3.
Select the macro you want to run. In this case, there is only
one and it is already highlighted. Then click on Run.




FIGURE 16“3




TLFeBOOK
Chapter 16
276




Setting (and Resetting) the Shortcut Key
This dialog box will also give you the option of setting a new
shortcut key or changing one that you have already assigned.
Click on the Options button at the lower right-hand corner to
see the dialog box in Figure 16-4. Type in the new shortcut key
letter you want.



TURNING ON THE VB TOOLBAR
If you are going to do a lot of work with recording macros, or
with the VBA code later on, it helps to have the Visual Basic
toolbar visible in the toolbars at the top of the screen. Do the
sequence View > Toolbars > Visual Basic and the Visual Basic
toolbar will appear in the middle of the screen. It is shown in
Figure 16-5.
You can drag this into the toolbar section and park it
there. The important controls for what we need are the arrow
key (o), which is the icon to run a macro, and the circle (f),
which is the record macro icon. When the recording mode is
on, the circle will change to a square (g), which is the stop
recording icon.


FIGURE 16“4




TLFeBOOK
Recording Macros 277




FIGURE 16“5




LOOKING AT WHAT WE RECORDED
Let™s look at what we recorded. To do that, we have to go to the
Visual Basic Editor (VBE). Every Excel file has two parts to it: the
worksheet part, which is the worksheet part everyone sees when
Excel first opens up on the computer screen, and the VBE, which
can hold the VBA code.

Let™s Go to the VBE
To go to the VBE, use either one of these steps:
Tools > Macro >Visual Basic Editor
u

or
Alt þ F11
u


Once you do this, you will see the screen that appears as Figure 16-6.

PARTS OF THE VBE
There are three main areas in the VBE:
1. Top left corner: This is the Project Explorer window,

<<

. 50
( 62 .)



>>