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.


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.

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)

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

Absolute / Relative Recording

When you record a macro it will be recorded with "absolute recording" by default.
This is an example of some code that has been recorded using Absolute Recording:

Range("B2").Select 
ActiveCell.Value = "some text"
Range("B4").Select

When you select (or move) from one cell to another the exact cell range will be used.
You can switch to Relative Recording at any time by using the toggle button on the Stop Recording toolbar.
This is an example of some code that has been recorded using Relative Recording:

ActiveCell.Offset(-2,2).Range("A1").Select 
ActiveCell.Value = "some text"
ActiveCell.Offset(10,-3).Range("A1").Select

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext