Recording Excel Macros with Absolute References - dummies

Recording Excel Macros with Absolute References

By Michael Alexander

Excel’s default recording mode is absolute reference. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.

The best way to understand how this concept applies to macros is to try it out. Record a macro that counts the rows in a worksheet. (See the figure for an example.)

Your pretotaled worksheet containing two tables.
Your pretotaled worksheet containing two tables.

You can find the sample data set used here online.

Follow these steps to record the macro:

  1. Make sure cell A1 is selected.

  2. On the Developer tab, select Record Macro.

  3. Name the macro AddTotal.

  4. Choose This Workbook for the save location.

  5. Click OK to start recording.

    At this point, Excel is recording your actions.

  6. While Excel is recording, select cell A16 and type Total in the cell.

  7. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).

    This formula gives a count of branch numbers at the bottom of column D. You use the COUNTA function because the branch numbers are stored as text.

  8. Click Stop Recording on the Developer tab to stop recording the macro.

    Your post-totaled worksheet.
    Your post-totaled worksheet.

The formatted worksheet should look like something like the one shown here.

To see your macro in action, delete the Total row you just added and play back your macro by following these steps:

  1. On the Developer tab, select Macros.

  2. Find and select the AddTotal macro you just recorded.

  3. Click the Run button.

If all goes well, the macro plays back your actions to a T and gives your table a total. Now here’s the thing. No matter how hard you try, you can’t make the AddTotal macro work on the second table. Why? Because you recorded it as an absolute macro.

To understand what this means, examine the underlying code by selecting Macros on the Developer tab. The Macro dialog box appears.

The Excel Macro ­dialog box.
The Excel Macro ­dialog box.

Select the AddTotal macro and click the Edit button. Visual Basic Editor opens and displays the code that was written when you recorded your macro:

Sub AddTotal()
  ActiveCell.FormulaR1C1 = "Total"
  ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"
End Sub

Pay particular attention to the two lines of code that select range A16 and range D16. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute cell references. In other words, no matter where your cursor is in your workbook, when you run the recorded macro, Excel will select cell A16 and then cell D16. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.