Page 51 - Excel Progamming Weekend Crash Course

d540629 ch02.qxd 9/2/03 9:27 AM Page 26

26 Friday Evening

Please refer to the section “Code and Project Organization” earlier in this
session for more details on how and where VBA code is stored.
Cross-Ref

Cell References in Macros
The small toolbar that is displayed while a macro is recording contains one other button in
addition to the Stop button. This is the Relative Reference button. To use this feature, you
need to know something about how Excel refers to worksheet cells.
In a macro — or in any VBA code for that matter — it is often necessary to refer to spe-
cific cells in the worksheet. There are two ways this can be done. The first is by absolute ref-
erence, which identifies a specific cell by its absolute row and column position. An absolute
reference to cell A2, for example, always refers to that cell and never to any other cell.
The second type of reference is a relative reference that identifies a cell by its position rel-
ative to the currently active cell (the cell with the thick black border). For example, “one cell
down” is a relative reference, as is “two cells down and three cells to the right.” Clearly, the
specific cell referred to by a relative reference depends on the location of the active cell.

Cell references are covered in more detail in Session 11.

Cross-Ref
Excel defaults to recording macros using absolute references. Suppose, for example, you
started with cell A1 active and recorded a macro that consisted of pressing the down arrow
key four times. The resulting macro would contain the VBA equivalents of the following
commands:
Go to cell A2
Go to cell A3
Go to cell A4
Go to cell A5

If, during macro recording, you click the Relative Reference button, Excel switches to
using relative references (click the button again to return to absolute reference recording).
With relative references, the same macro would be recorded as the VBA equivalents of these
commands:

Go down one cell
Go down one cell
Go down one cell
Go down one cell
Whether you decide to use absolute or relative references, or a combination of the two,
depends on the needs of your program.