Excel 2019 Bible

Chapter 42: Introducing Visual Basic for Applications

42

Test this new macro, and you will see that it performs as it should.

Absolute versus relative recording

If you’re going to work with recorded macros, you need to understand the concept of rela-

tive versus absolute recording modes. In the previous example, we showed how even a

simple macro could act unexpectedly because of an incorrect recording mode.

Normally, when you record a macro, Excel stores exact references to the cells that you

select. (That is, it performs absolute recording.) If you press Enter while in cell F14 and

the active cell moves down one, the recorded macro will show that you selected cell F15.

Similarly, if you were to select cells B1:B10 while you’re recording a macro, for example,

Excel records this selection as follows:

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.

In the Developer ➪ Code group of the Ribbon there is a Use Relative References control.

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 dif-

ferently, 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. 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 starting point and then stores relative references to this cell. As a result, you get differ-

ent 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 References control appears with

a background color. To return to absolute recording, click the Use Relative References con-

trol again and it displays its normal state, with no background color.

Another example

In the first example, the macro behaved oddly by selecting cell F15 after it entered the

name. This odd behavior didn’t cause any harm or cause the macro not to execute properly.

This example demonstrates how choosing the wrong recording mode can actually cause the

macro to work incorrectly. You will record a macro that inserts the current date and time

into the active cell. To create the macro, follow these steps:

1. Activate an empty cell.


2. Choose Developer ➪ Code ➪ Record Macro. The Record Macro dialog box appears.