How to Record Macros in Relative Mode and Absolute Mode in Excel 2016 - dummies

How to Record Macros in Relative Mode and Absolute Mode in Excel 2016

By John Walkenbach

When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) But quite often, this is the wrong recording mode. If you use absolute recording mode, Excel records actual cell references. If you use relative recording, Excel records relative references to cells. Keep reading to see the difference.

Recording in absolute mode

Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names in a worksheet:

  1. Make sure that the Developer  →  Code  →  Use Relative References button is not highlighted and then choose Developer  →  Code  →  Record Macro.

  2. Type Absolute as the name for this macro.

  3. Click OK to begin recording.

  4. Activate cell B1, and type Jan in that cell.

  5. Move to cell C1, and type Feb.

  6. Move to cell D1, and type Mar.

  7. Click cell B1 to activate it again.

  8. Stop the macro recorder.

  9. Press Alt+F11 to activate the VBE.

  10. Examine the Module1 module.

    Excel generates the following code:

    Sub Absolute()
    ‘ Absolute Macro
      ActiveCell.FormulaR1C1 = “Jan”
      ActiveCell.FormulaR1C1 = “Feb”
      ActiveCell.FormulaR1C1 = “Mar”
    End Sub

    When executed, this macro selects cell B1 and inserts the three month names into the range B1:D1. Then the macro reactivates cell B1.

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 when it is executed. In this case, the macro always enters the names of the first three months in the range B1:D1 on the active worksheet.

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.

You can change the manner in which Excel records your actions by clicking the Use Relative References button in the Code group of the Developer tab. This button is a toggle button. When the button appears highlighted in a different color, 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, delete the contents of range 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 from the rest of the ribbon.

  6. Type Jan in cell B1.

  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 that Excel generates is quite different from the code generated in absolute mode:

Sub Relative()
‘Relative Macro
  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.