MS-Excel / General Formatting

---

Recording Relative Macro

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.

You can change the manner in which Excel records your actions by clicking the Use Relative References button on the Code group on the Developer tab. This button is a toggle button. 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 Developer> Code> Record Macro.
3. Name this macro Relative.
4. Click OK to begin recording.
5. Click the Use Relative References button to change the recording mode to relative.
   When you click this button, it changes to a different color than the rest of the ribbon.
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 Rodi Ken
'
    ActiveCell.FormulaR1C1 = "Jan"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Feb"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Mar"
    ActiveCell.Offset(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.