. 51
( 62 .)


which shows you the items that the VB Editor has. If you
do not see this window on your screen, or if you inad-
vertently closed it by clicking on the X box at its top right
corner, you can make it reappear by going through this
sequence in the VB Editor (and not in the worksheet part):
View > Project Explorer.
2. Bottom left corner: This is the Properties window. It
will show the properties of the item being highlighted in
the Project Explorer window. To make this window
appear if it is not there, use: View > Properties Window.
3. Right side of the screen: This is the Code Editor
window. This is the window that will show the macros

Chapter 16


and where they can be edited. To make this window
appear if it is not there, use: View > Code.

Moving Between VBE and the Worksheets
Note that on the taskbar at the bottom of the screen, there are
now two buttons representing the active components of Excel:
the worksheet part and the VBE part.
You can now move between these two parts of the file by
simply clicking on a button. If you are in VBE, clicking on the
worksheet button will shift you there, and the VBE will remain
active”but in the background. When you want to go back to the
VBE again, click on the taskbar button for the VBE and you are
back there again.

Recording Macros 279

If you are in the VBE and you press Alt þ Q or you click
on the X at the top right of the screen, you will close the VBE
and then be back in the worksheet part. Of course, if you do it
this way, if you want to get back to the VBE, you will have to
restart it.

Click on the Modules in the Project Explorer window, and then
on Module 1 (see Figure 16-7). You will see the code appear in
the Code Editor window on the right. Here is what it looks like:
Sub Macro1 ()
˜Macro1 Macro
˜Keyboard Shortcut: Ctrl þ Shift þ T
Range (“C1”).Select
ActiveCell.FormulaR1C1 ¼ “10”
Range (“C2”).Select
ActiveCell.FormulaR1C1 ¼ “20”
Range (“C3”).Select
ActiveCell.FormulaR1C1 ¼ “30”
Range (“C5”).Select
ActiveCell.FormulaR1C1 ¼ “¼SUM(R[À4]C:R[À2]C)”
Range (“C1:C5”).Select
Range (“D1”).Select
Range (“D5”).Select
Selection.Font.Bold ¼ True
Selection.Font.Italic ¼ True
Range (“A1”).Select
End Sub

Chapter 16


At first glance, this looks like gobbledygook. But just a little
study will reveal how it works. VBA code is wonderful in one
aspect: its code is reasonably close to normal English language.
So let™s try to decipher it line by line. Here is the translation in
plain English of what each line means:

Select cell C1.

In the active cell (i.e., the one that we just
ActiveCell.FormulaR1C1 = “10”
selected, C1), enter the formula ˜˜10.™™
Because there is no equal (¼) sign within
the double quotes, the code enters the
number 10 in this cell.

Select cell C2. In the active cell (C2),
enter the number 20.
ActiveCell.FormulaR1C1 = “20”

Select cell C3. In the active cell (C3),
enter the number 30.
ActiveCell.FormulaR1C1 = “30”

Recording Macros 281

Select cell C5.

This code writes the formula that begins
ActiveCell.FormulaR1C1 =
with ¼SUM and then de¬nes the rows
from 4 rows above C5 to 2 rows above
C5 and adds the closing parenthesis. If
you remember, we were writing a formula
for SUM(C1:C3).

Select the range of 5 rows, and Copy.

Select Sheet2, and then select D1 and

Go to cell D5, apply Bold and Italic format-
ting, and then go to cell A1.

By the way, this macro has a shortcoming that you may or
may not have noticed. Remember that the series of actions we
were recording was about writing some simple formulas on
Sheet1 and then copying them onto Sheet2. However, because
we were already on Sheet1 when we started recording, the
macro has no line that says


Consequently, if you were on another sheet and you
launched the macro, the first block of formulas would be written
on that active sheet, and not on Sheet1.
We can remedy this situation by adding our own code at
the very top of this code. Just add the line at the very top of
the macro so that it looks like what you see below. You can
add a comment at the end of the line by first typing an apos-
trophe before entering your comments. The apostrophe tells
VBA to disregard any text that follows it. You can write a whole
line as a comment; just enter the apostrophe at the very beginning
of the line. Once you have this line in, you can be anywhere in the

Chapter 16

file and the macro will write the formulas only on Sheets1 and
Sub Macro1 ()
˜Macro1 Macro
˜Keyboard Shortcut: Ctrl þ Shift þ T
Sheets(“Sheet1”).Select ˜This is the new line
we are adding
ActiveCell.FormulaR1C1 ¼ “10”
ActiveCell.FormulaR1C1 ¼ “20”

Once you have seen the recorded VBA code, you may notice that it
gets to be repetitious. In fact, it is, and that is the result of the
recording system that is transcribing in the most accurate way pos-
sible all the steps that you are specifying from the keyboard. When
you do write your own code, you can directly write the code in a
way that is more streamlined than what the VBE is producing.
Following are some beginning hints on streamlining the code.

Entering Values into a Cell

ActiveCell.FormulaR1C1 ¼ “10”

The two steps described here involve bringing the cursor to the
cell, and then entering a value into it. In VBA, you can enter a
value directly into a cell without selecting it, so this could be
simplified into:

.FormulaR1C1 ¼ “10”

Recording Macros 283

An even simpler way to do this is to write:

.Value ¼ “10”

Anytime you wish to specify a value, Value will work as
well as FormulaR1C1. You can even simplify it further and
write Range(“C1”) ¼ “10” but it is better as a programming
style to specify what it is you want with that Range. In this
case, let ˜˜the value of the Range be™™ x, so you should always
specify Value. By the way, you should also use Value even if
you are entering text. That™s a ˜˜value,™™ too, according to VBA.

Writing Formulas
The recorder constructs the code based on relative addresses.
This is why you see


. 51
( 62 .)