USING ABSOLUTE AND RELATIVE REFERENCES

In the MyPolyAddress macro, Excel recorded references as absolute references in the A1 format (as opposed to the R1C1 format). As a result, the MyPolyAddress macro always enters my name name in A2 and the address in A3, A4 and A5.

In VBA, cell selections are recorded as absolute references unless you click the Relative Reference button on the Stop Recording toolbar. If you click this button, the recorder begins recording relative references. If you click the button again, the recorder records subsequent actions using absolute references—and so on. You can toggle between relative and absolute references at any time while recording.

Tip: Check the Relative Reference button before you record

The Relative Reference button on the Stop Recording toolbar appears pressed in when selected. This is your indication that the recorder is now using relative references. There is no other indication, unfortunately. The button’s ScreenTip does not change to say Absolute Reference. Because it’s important to know which mode you’re in when you record a macro, it’s a good idea to check the appearance of the button before you start.

Suppose that instead of inserting my name and address in A2:A5 of the active worksheet, you’d like to insert the address beginning at whatever cell happens to be active when you run the macro. To do this, you need to create a new MyPolyAddress macro—we’ll call it MyPolyAddressRel—that uses relative references instead of absolute references:

  1. Clear my name and address from A2:A5 of Sheet1.
  2. Choose Tools, Macro, Record New Macro. In the Record Macro dialog box, enter MyPolyAddressRel for the macro’s name and lowercase a for the shortcut key. Type Enter my Poly address in any cell in the Description box, and click OK. Excel displays the Stop Recording toolbar.
  3. Click the Relative Reference button on the Stop Recording toolbar.
  4. Choose Format, Cells, select 14-point Arial bold italic, and then click OK.
  5. In the current cell, enter Prof. Ming Leung. In the cell below, enter Polytechnic University , and in the cell below that, enter Six Metrotech Center, and in the cell below that, enter Brooklyn, NY 11201.
  6. Click the Stop Recording button.
Test this macro by clicking in various cells and pressing Ctrl+a (lowercase this time). If you now choose Tools, Macro, Macros, select MyPolyAddressRel, and click Edit, you’ll see the following listing:
Sub MyPolyAddressRel()
With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold Italic"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Polytechnic University"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Six Metrotech Center"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Brooklyn, NY 11201"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
The original macro enters my name and address in the range A2:A5, regardless of which cell is active when you start the macro. The new macro, on the other hand, enters the address starting in the active cell, no matter where that cell is located.

If you compare the two versions of the macro, you’ll see that the only difference between them lies in the statements used to select cells. For example, the new version of the macro does not include a statement for selecting the first cell, because it uses relative references and the first cell is already selected.

In addition to selecting the second cell, the original macro uses the statement 
Range("A3").Select
whereas the new version uses 
ActiveCell.Offset(1,0).Range("A1").Select
To move from the active cell to the cell below it in the new macro, VBA starts with the Active- Cell object, to which it applies the Offset method with two arguments for the number of rows and columns to offset. The Range keyword then returns a range with the same dimensions as its argument. In this case, the argument “A1” specifies that we want a range consisting of a single cell.

Finally, the Select method selects the range, as in the original macro.

Which form is better—absolute or relative? It depends.

Absolute references are useful when you want to perform the same action in the same spot in several worksheets, or when you want to perform the same action repeatedly in the same part of one worksheet.

Relative references are useful when you want to perform an action anywhere in a worksheet.