<<

. 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




TLFeBOOK
Chapter 16
278




FIGURE 16“6




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.




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



READING THE CODE WE
RECORDED
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
Selection.Copy
Sheets(“Sheet2”).Select
Range (“D1”).Select
ActiveSheet.Paste
Range (“D5”).Select
Selection.Font.Bold ¼ True
Selection.Font.Italic ¼ True
Range (“A1”).Select
End Sub




TLFeBOOK
Chapter 16
280




FIGURE 16“7




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.
Range(“C1”).Select

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),
Range(“C2”).Select
enter the number 20.
ActiveCell.FormulaR1C1 = “20”

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




TLFeBOOK
Recording Macros 281




Select cell C5.
Range(“C5”).Select

This code writes the formula that begins
ActiveCell.FormulaR1C1 =
with ¼SUM and then de¬nes the rows
“=SUM(R[-4]C:R[-2]C)”
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.
Range(“C1:C5”).Select
Selection.Copy

Select Sheet2, and then select D1 and
Sheets(“Sheet2”).Select
Paste.
Range(“D1”).Select
ActiveSheet.Paste

Go to cell D5, apply Bold and Italic format-
Range(“D5”).Select
ting, and then go to cell A1.
Selection.Font.Bold=True
Selection.Font.Italic=True
Range(“A1”).Select




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

Sheets(“Sheet1”).Select

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




TLFeBOOK
Chapter 16
282




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



EDITING THE CODE WE RECORDED
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

Range(“C1”).Select
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”
Range(“C1”)




TLFeBOOK
Recording Macros 283




An even simpler way to do this is to write:

.Value ¼ “10”
Range(“C1”)

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



>>