Excel 2010 Bible

Chapter 39: Introducing Visual Basic for Applications

809

Normally, when you record a macro, Excel stores exact references to the cells that you select. (That

is, it performs absolute recording.) If you select the range B1:B10 while you’re recording a macro,

for example, Excel records this selection as

Range(“B1:B10”).Select

This VBA statement means exactly what it says: “Select the cells in the range B1:B10.” When you

invoke the macro that contains this statement, the same cells are always selected, regardless of

where the active cell is located.

Look in the Developer ➪ Code group of the Ribbon for Use Relative References. When you click

this control, Excel changes its recording mode from absolute (the default) to relative. When

recording in relative mode, selecting a range of cells is translated differently, depending on where

the active cell is located. For example, if you’re recording in relative mode and cell A1 is active,

selecting the range B1:B10 generates the following statement:

ActiveCell.Offset(0, 1).Range(“A1:A10”).Select

This statement can be translated as “From the active cell, move 0 rows down and 1 column right,

and then treat this new cell as if it were cell A1. Now select what would be A1:A10.” In other

words, a macro that is recorded in relative mode starts out by using the active cell as its base and

then stores relative references to this cell. As a result, you get different results, depending on the

location of the active cell. When you replay this macro, the cells that are selected depend on the

active cell. This macro selects a range that is 10 rows by 1 column, offset from the active cell by 0

rows and 1 column.

When Excel is recording in relative mode, the Use Relative Reference control appears depressed.

To return to absolute recording, click the Use Relative Reference control again (and it displays its

normal, undepressed state).

Storing macros in your Personal Macro Workbook

Most user-created macros are designed for use in a specific workbook, but you may want to use

some macros in all your work. You can store these general-purpose macros in the Personal Macro

Workbook so that they’re always available to you. The Personal Macro Workbook is loaded when-

ever you start Excel. This file, named personal.xlsb, doesn’t exist until you record a macro,

using Personal Macro Workbook as the destination.

Note
The Personal Macro Workbook normally is in a hidden window (to keep it out of the way). n

To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook

option in the Record Macro dialog box before you start recording. This option is in the Store

Macro In drop-down box.