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 Relative references in Excel VBA.  We select a cell “A1”, turn on “Use Relative Reference” and record a macro to type some text in cells B2:B4.  

Since we turn on the “Relative reference” option.  Macro considers the number of rows and columns from active cells.  In our example, we select cell A1 and start type B2, which is to move one column and one row from A1 (Active cell).

What are Relative References?

In Excel, the way of recording actions within a macro that is relative to the cell or range that the macro starts from is known as a relative reference. When you start recording a macro with relative reference, Excel records the actions based on the position of the active cell at the beginning of the macro. The recorded actions will adjust accordingly when the macro is run on different ranges.

Using Relative 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 relative references.

Implementation

Follow the below steps to implement relative reference 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. 

Step 2. Go to “Developer” Tab >> Press “Use Relative References” >> Click “Record Macro”

Step 3. Enter the Macro name “relativeReference” and Press “OK”.

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.

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

VBA Code (Recorded)

Sub relativeReference()

    ActiveCell.Offset(1, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Australia”

    ActiveCell.Offset(1, 0).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Brazil”

    ActiveCell.Offset(1, 0).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Mexico”

    ActiveCell.Offset(1, 0).Range(“A1”).Select

End Sub 

Step 8. You just delete the contents in cells B2:B4, Select Cell B1.

Step 9. Go to View >> Macros >> View Macros – to popup Macro dialog 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. Select Macro from the list (eg. relative reference) and Press “Run”.

Output

The active cell is B1 and runs the macro. So, the outputs (C2:C4) are placed in one row and one column from the active cell B1.

Reasons for Relative Reference Not Working

In Excel, relative referencing occasionally fails to function. The “Use Relative References” function being disabled is one potential explanation for relative referencing not being functional.

Solution

To fix the issue, press the “Use Relative References” button from the Developer menu before carrying out any actions.

I covered Excel Macro Relative Reference in this post. Hopefully, it would benefit everyone. I advise you to read more articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!

FAQs on Relative References in Excel Macros

Q1: What is Relative Reference in Excel Macros?

Answer:

In Excel, the way of recording actions within a macro that is relative to the cell or range that the macro starts from is known as a relative reference. When you start recording a macro with relative reference, Excel records the actions based on the position of the active cell at the beginning of the macro. The recorded actions will adjust accordingly when the macro is run on different ranges.

Q2: What are the benefits of using Relative Reference in Excel?

Answer:

Below are some benefits of Relative Reference in Excel:

1. Flexibility: Relative reference can be applied to various cells or ranges.
2. Automatic Efficiency: Using Relative references reduces the need for manual editing.
3. Maintainability: Relative reference makes macros easy to maintain. If changes made to the data or layout occur, the macro will function correctly.

Q3: How we can use Relative Reference in a Macro?

Answer:

Follow the below steps to use Relative Reference in macro:

Step 1: Go to the “View Tab” in the Excel Ribbon.

Step 2: Click on “Relative Reference” in the Macros group.

Q4: What is the difference between relative references and absolute references in Excel Macros?

Answer:

Relative references adjust based on the position of the active cell when the macro is started, making them adaptable to different situations.

Whereas Absolute Reference does not change when the macro is run on different cells or ranges, making them less versatile and more rigid.

Q5: Is switching possible between relative and absolute reference in the same macro?

Answer:

No, you cannot switch between both the reference in the same macro. When you start recording a macro with relative reference, all recorded actions will be relative to the starting cell or range. If you start with the absolute reference, you can use absolute references.