Absolute References in Excel Macros

We have two options to refer a cell in excel VBA Absolute references and Relative references.  Default Excel records macro in Absolute mode.

In this article, we learn about absolute references in excel VBA.  We record a macro to type some text in cells B2:B4.  Macro always types the text in the cells B2:B4 irrespective of the active cell.

Implementation:

Follow the below steps to implement Absolute references in excel macros:

Step 1: Open Excel and Select Cell “A1”.

Step 2: Go to “Developer” Tab >> Click “Record Macro”. 

Step 3: Enter the Macro name “absoluteReference” and Press “OK”

Step 4: Type “Australia” in cell B2.

Step 5: Type “Brazil” in cell B3.

Step 6: Type “Mexico” in cell B4.

Step 7: Select cell B5 and Press “Stop Recording”.  

VBA Code (Recorded):

Sub absoluteReference()
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Australia"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Brazil"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "Mexico"
    Range("B5").Select
End Sub

Step 8: You just delete the contents in cells B2:B4.

Step 9: Go to View >> Macros >> View Macros – to popup Macro dialog box [keyboard shortcut – Alt+F8].

Step 10: Select Macro from the list (eg. absoluteReference) and Press “Run”.

Output:

My Personal Notes arrow_drop_up

Save