Absolute References in Excel Macros are fixed cell references used in Excel VBA that always point to the same specific cells, regardless of the active cell or where the macro is executed. They ensure that recorded macro actions—such as entering data, applying formulas, or formatting, are performed in the exact same cell locations every time the macro runs.
Ensuring Absolute References
We must make sure that the macro is recorded starting from the cell where the steps must begin in order to record a macro with absolute references.
Implementation
Follow the steps below to implement Absolute references in Excel macros:
Step 1: Open Excel and Select Cell “A1”
Note: The macro will place whatever you recorded on the same worksheet in the same location if you do not create a new worksheet before running it. You do not want this. Every report needs to be on a different worksheet.
Recording a Macro
The Record Macro command, located on the ribbon under the VIEW tab Macros, allows you to begin recording the macro.
On the left side of the Excel taskbar, there is a button that says "Start Recording Macro" which you can also use.
Step 2: Go to the “Developer” Tab >> Click “Record Macro”
Give the macro a name that will help others recognize it as a report for a certain project.
Step 3: Enter the Macro name “absolute Reference” and Press “OK”
Your macro begins to record
Step 4: Type “Australia” in cell B2
Step 5: Type “Brazil” in cell B3
Step 6: Type “Mexico” in cell B4
Stop recording the macro
Either use the Stop Recording command located on the ribbon under the VIEW tab Macros or click the Stop Recording Macro button located on the left side of the Excel taskbar to stop recording the macro.
Click on cell B5. This makes sure that the macro always records your steps in B5.
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: We can just delete the contents in cells B2:B4
Step 9: Go to View >> Macros >> View Macros – to pop-up Macro dialogue box [keyboard shortcut - Alt+F8]
Running a Macro
Simply by running the macro, you may create any number of reports in a matter of seconds.
Step 10: On the Ribbon, select the VIEW button and Click Macros
Step 11: Select a Macro from the list (e.g. absolute Reference) and Press “Run”
Output
Importance of Absolute Reference in Excel Macros
Following are some points:
- Consistent Result: By using absolute references, you ensure that your macro consistently performs calculations or actions on specific cells, no matter where it is applied.
- Flexibility in Positioning: With absolute references, you have the flexibility to place your data and formulas in specific locations within the worksheet.
- Enhanced automation: Absolute reference makes your macros more suitable for automated tasks and batch processing.