Absolute References in Excel Macros

Excel Macros are incredibly powerful tools that provide the capability to automate repetitive tasks, streamline processes, and save time. When building macros, it’s common to use cell references for performing calculations or actions. In some situations, it’s important to ensure that these references remain fixed, no matter where the macro is applied or copied. Here the absolute reference plays an important role.

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

Both absolute references and relative references can be used while recording Excel macros. Regardless of the active cell, a macro recorded using absolute references places the recorded steps exactly in the cells where it was recorded. However, a macro that has been recorded with relative references can carry out the activities at several locations on the worksheet.

In this article, we will 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.

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 below steps 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:

1. 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.
2. Flexibility in Positioning: With absolute references, you have the flexibility to place your data and formulas in specific locations within the worksheet.
3. Enhanced automation: Absolute reference makes your macros more suitable for automated tasks and batch processing.

FAQs on Absolute Reference in Excel Macros

Q1. Which form is preferable, absolute or relative?

Answer:

It depends. Absolute references are in handy when you wish to repeat the same action in the same location across multiple worksheets, or even just the same area of a single worksheet.

When you need to do an action anywhere in a worksheet, relative references come in handy.

Q2. What is the benefit of using absolute cell references?

Answer:

When filling up cells, there may be situations when you don’t want a cell reference to change. Absolute references don’t change when copied or filled in, in contrast to relative references. To maintain the consistency of a row and/or column, utilize an absolute reference.

Q3: Is it possible to mix relative and absolute references in Excel Macros?

Answer:

Yes, we can mix relative and absolute references in Excel macros. A combination of relative and absolute references can help us to create flexible macros that adjust to different cell locations while maintaining fixed references where ever needed.

Q4: How can we test and debug macros with absolute references?

Answer:

You can use the macro step-by-step with the macro debugger or add message boxes to display variable values. This will also help you to verify that the absolute reference is working as intended.