BET: Microsoft Excel Macros - Absolute or Relative References

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Macros > Absolute or Relative References

< Previous | Next >

Step 1 - Absolute or Relative

	During macro recording cell references can be made either relative to the start position or with an absolute address.
	By default, recorded macros use absolute cell referencing.
	This means the exact cell references are recorded into the macros.
	You can switch back and forth between these two macro recording settings as many time as you want.
	When you record a macro it will be recorded with "absolute recording" by default.
	To emphasis the two different settings we are going to perform some very simple formatting to a table of data.
	First using absolute references and then using relative references.

Step 2 - Absolute Cell References

Select cell "B2" before you select (Tools > Macros > Record New Macro).

1) In the Macro Name box type "Absolute".

2) Select the cells "C3:C7".

3) Choose a colour using the Fill Color button on the Formatting toolbar.

4) Select the cells "D3:D7".

5) Choose another colour using the Fill Color button on the Formatting toolbar.

6) Press the bold and italic buttons on the formatting toolbar.

7) Select the cells "E3:E7".

8) Choose another colour using the Fill Color button on the Formatting toolbar.

9) Press the Stop Recording button on the Stop Recording toolbar.

	If the Stop Recording toolbar did not appear automatically when you started recording you can display it by selecting (View > Toolbars > Stop Recording).
	This recorded macro produces the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Sub Absolute()
    Range("C3:C7").Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    Range("D3:D7").Select
    With Selection.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With
    Selection.Font.Italic = True
    Range("E3:E7").Select
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
End Sub

Step 3 - Relative Cell References

Select cell "B2" before you select (Tools > Macros > Record New Macro).

10) In the Macro Name box type "Relative".

11) Select the Relative References button on the Stop Recording toolbar.

12) Repeat exactly the same steps 2-8 as above.

13) Select the Relative References button again to switch back to Absolute References.

14) Press the Stop Recording button on the Stop Recording toolbar.

This recorded macro produces the following code:

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

Sub Relative()
    ActiveCell.Offset(1, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    ActiveCell.Offset(0, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With
    Selection.Font.Italic = True
    ActiveCell.Offset(0, 1).Range("A1:A5").Select
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
End Sub

Step 4 - Understanding the Difference

	A lot of the code is the same but there is a difference between how the cells are selected.
	When you use absolute references, selecting or moving from one cell to another generates code that refers to that specific cell range.

37	Range("C3:C7").Select

In the case of relative references the code generated is a reference to a cell in relation to the active cell (i.e. the cell initially selected)

38	ActiveCell.Offset(1, 1).Range("A1:A5").Select

Step 5 - Stop Recording Toolbar

	If you manually displayed the Stop Recording toolbar then an additional line of code will have been recorded.
	You need to delete this line or prefix it with an apostrophe to prevent it from being included.

39	'Application.CommandBars("Stop Recording").Visible = True

Step 6 - Things to Remember

		The tooltip of the Relative References button does not change to indicate which setting is currently selected. The only indication is whether the button has a sunken appearance or not.
		You can switch to Relative Recording at any time by using the toggle button on the Stop Recording toolbar.
		Which ever setting is selected when you press the Stop Recording button becomes the default the next time you record a macro. If you stop recording with relative references then relative references will be recorded the next time. If you close Excel then the setting is reset to absolute references.

< Previous | Top | Next >

Leading the way in Microsoft Office Development

Step 1 - Absolute or Relative

During macro recording cell references can be made either relative to the start position or with an absolute address.

By default, recorded macros use absolute cell referencing.

This means the exact cell references are recorded into the macros.

You can switch back and forth between these two macro recording settings as many time as you want.

When you record a macro it will be recorded with "absolute recording" by default.

To emphasis the two different settings we are going to perform some very simple formatting to a table of data.

First using absolute references and then using relative references.

Step 2 - Absolute Cell References

Select cell "B2" before you select (Tools > Macros > Record New Macro).

1) In the Macro Name box type "Absolute".

2) Select the cells "C3:C7".

3) Choose a colour using the Fill Color button on the Formatting toolbar.

4) Select the cells "D3:D7".

5) Choose another colour using the Fill Color button on the Formatting toolbar.

6) Press the bold and italic buttons on the formatting toolbar.

7) Select the cells "E3:E7".

8) Choose another colour using the Fill Color button on the Formatting toolbar.

9) Press the Stop Recording button on the Stop Recording toolbar.

If the Stop Recording toolbar did not appear automatically when you started recording you can display it by selecting (View > Toolbars > Stop Recording).

This recorded macro produces the following code:

Step 3 - Relative Cell References

Select cell "B2" before you select (Tools > Macros > Record New Macro).

10) In the Macro Name box type "Relative".

11) Select the Relative References button on the Stop Recording toolbar.

12) Repeat exactly the same steps 2-8 as above.

13) Select the Relative References button again to switch back to Absolute References.

14) Press the Stop Recording button on the Stop Recording toolbar.

This recorded macro produces the following code:

Step 4 - Understanding the Difference

A lot of the code is the same but there is a difference between how the cells are selected.

When you use absolute references, selecting or moving from one cell to another generates code that refers to that specific cell range.

In the case of relative references the code generated is a reference to a cell in relation to the active cell (i.e. the cell initially selected)

Step 5 - Stop Recording Toolbar

If you manually displayed the Stop Recording toolbar then an additional line of code will have been recorded.

You need to delete this line or prefix it with an apostrophe to prevent it from being included.

Step 6 - Things to Remember

The tooltip of the Relative References button does not change to indicate which setting is currently selected. The only indication is whether the button has a sunken appearance or not.

You can switch to Relative Recording at any time by using the toggle button on the Stop Recording toolbar.

Which ever setting is selected when you press the Stop Recording button becomes the default the next time you record a macro. If you stop recording with relative references then relative references will be recorded the next time. If you close Excel then the setting is reset to absolute references.