Recording in relative mode

In some cases you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording.

The Stop Recording toolbar, which consists of only two buttons, is displayed when you are recording a macro. You can change the manner in which Excel records your actions by clicking the Relative Reference button on the Stop Recording toolbar. This button is a toggle. When the button appears in a pressed state, the recording mode is relative. When the button appears normally, you are recording in absolute mode.

You can change the recording method at any time, even in the middle of recording.

To see how relative mode recording works, erase the cells in B1:D1 and then perform the following steps:

1. Activate cell B1.

2. Choose ToolsOMacroORecord New Macro.

3. Name this macro Relative.

4. Click OK to begin recording.

5. Click the Relative Reference button to change the recording mode to relative.

When you click this button, it looks pressed.

6. Activate cell B1 and type Jan in that cell.

7. Move to cell C1 and type Feb.

8. Move to cell D1 and type Mar.

9. Select cell B1.

10. Stop the macro recorder.

Notice that this procedure differs slightly from the previous example. In this example, you activate the beginning cell before you start recording. This is an important step when you record macros that use the active cell as a base.

This macro always starts entering text in the active cell. Try it. Move the cell pointer to any cell and then execute the Relative macro. The month names are always entered beginning at the active cell.

With the recording mode set to relative, the code Excel generates is quite different from absolute mode:

Sub Relative()

' Relative Macro

' Macro recorded by John Walkenbach

ActiveCell.FormulaR1C1 = "Jan"

ActiveCell.0ffset(0, 1).Range("A1")

.Select

ActiveCell.FormulaR1C1 = "Feb"

ActiveCell.0ffset(0, 1).Range("A1")

.Select

ActiveCell.FormulaR1C1 = "Mar"

ActiveCell.0ffset(0, -2).Range("A1"

).Select

End Sub

To test this macro, activate any cell except B1. The month names are entered in three cells, beginning with the cell that you activated.

Notice that the code generated by the macro recorder refers to cell A1. This may seem strange because you never used cell A1 during the recording of the macro. This is simply a byproduct of the way the macro recorder works. (I discuss this in more detail in Chapter 8 where I talk about the Offset method.)

0 0

Post a comment