Excel

Absolute and Relative Macro Recording

When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) But sometimes this is not the right recording mode for a situation. Excel records actual cell references when we use absolute recording mode. If we use relative recording mode, Excel records relative references to cells. To change the macro recording mode, click the Developer tab, and press the Use Relative Reference button in the Code group. The dark gray color of that button indicates the relative recording mode is activated and if the color is same as the other buttons, the macro will record in absolute reference mode.

Use Relative References

Macro Recording – Absolute References

Open a new workbook and follow these steps to record a simple macro in absolute mode. Before start recording macro, make sure that the Use Relative References button is not highlighted. This macro simply enters the Monday, Tuesday and Wednesday in a worksheet.

  1. Click Record Macro button, type Absolute as the name for this macro and click OK to begin recording.
  2. Activate cell A1, and type Monday.
  3. Move to cell A2, and type Tuesday.
  4. Move to cell A3, and type Wednesday.
  5. Click cell A1 to activate it again and stop the macro recording.

Press Alt+F11 to activate the VBE to examine the Module1 module:

VBE Absolute Macro

Excel generates the following code:

 Sub AbsoluteMacro()
 '
 ' Absolute Macro
 '
     Range("A1").Select
     ActiveCell.FormulaR1C1 = "Monday"
     Range("A2").Select
     ActiveCell.FormulaR1C1 = "Tuesday"
     Range("A3").Select
     ActiveCell.FormulaR1C1 = "Wednesday"
     Range("A1").Select
 End Sub

When executed, this macro selects cell A1 and inserts the Monday, Tuesday and Wednesday into the range A1:A3. Then the macro reactivates cell A1.

These same actions occur regardless of which cell is active when you execute the macro. A macro recorded by using absolute references always produces the same results on same range when it’s executed. In this case, the macro always enters the names of the first three days in the range A1:A3 on the active worksheet.

Record Macro – Use Relative References

Sometimes we want our recorded macro to work with cell locations in a relative references mode. For example, we want the macro to start entering the day names in the current active cell. In such a case, we need to use relative recording. To see how relative mode recording works, delete the contents of range A1:A3 and then perform the following steps:

Click the Use Relative References button to change the recording mode to relative from the Developer tab. Make sure that the Use Relative References button is highlighted:

Use Relative References

  1. Activate cell C1.
    As we don’t need to record 1st cell selection so we must activate the beginning cell (C1) before start the macro recording.
  2. Click Record Macro button, type Relative as the name for this macro and click OK to begin recording.
  3. Type Monday (in cell C1).
  4. Move to cell C2, and type Tuesday.
  5. Move to cell C3, and type Wednesday.
  6. Click cell C1 to activate it again and stop the macro recording.

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

Sub Relative()
 '
 ' Relative Macro
 '
 '
  ActiveCell.FormulaR1C1 = "Mon"
  ActiveCell.Offset(1, 0).Range("A1").Select
  ActiveCell.FormulaR1C1 = "Tue"
  ActiveCell.Offset(1, 0).Range("A1").Select
  ActiveCell.FormulaR1C1 = "Wed"
  ActiveCell.Offset(-2, 0).Range("A1").Select
 End Sub

This macro always starts entering text in the active cell. To test, activate any cell and then execute the Relative macro. The day names are always entered beginning at the active cell.

Notice that the code refers to cell A1 even we never used cell A1 during the macro recording. Don’t worry it is the byproduct behavior of macro recording.