Use Relative References in Excel VBA

By default, Excel records macros in absolute mode. However, sometimes it is useful to record macros in relative mode. This program teaches you how to do this. If you don't know how to record a macro, we highly recommend you to read this example first.

Recording in Absolute Mode

To record a macro in absolute mode, execute the following steps.

1. First, click Record Macro.

2. Next, select cell B3. Type Sales and press enter.

3. Type Production and press enter.

4. Type Logistics and press enter.

Result:

5. Click Stop Recording.

6. Empty Range("B3:B5").

7. Select any cell on the sheet and run the recorded macro.

Result:

A macro recorded in absolute mode always produces the same result.

Recording in Relative Mode

Wouldn't it be nice to place these words anywhere on the sheet automatically? Not just Range("B3:B5"). This would make the macro much more flexible. Solution: record the macro in relative mode.

1. Select "Use Relative References".

2. First, select any single cell (for example, cell B8).

3. Next, click Record Macro.

4. Type Sales and press enter.

5. Type Production and press enter.

6. Type Logistics and press enter.

Result:

7. Click Stop Recording.

8. Select any other cell (for example, cell D4) and run the recorded macro.

Result:

Excel places the words relative to the initial selected cell. That's why it's called recording in relative mode.