In this article, I will show you how to use relative references in Excel Macro while recording. You will learn how Excel Macro Relative Reference works after reading the article.
When you’re recording your own macros, you must know the concept of relative versus absolute recording modes. Because of the incorrect recording mode, a simple macro could fail to perform the intended job.
This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.
Download Practice Workbook
Download this practice workbook and practice while going through this article.
4 Quick Steps to Use Relative Reference in Excel Macro
Understanding the use of relative references in Excel macro is easy. For your convenience, I have divided the entire process into 4 easy steps. Let’s check the steps one by one.
Step 1: Activate Macro Recording Feature
The first step is to activate the macro recording feature. This will record the operations that you perform in Excel.
- Select cell A1.
- Then, go to the Developer
- After that, select Use Relative References.
- Finally, select Record Macro.
- The Record Macro box will appear.
- Give a Macro Name.
- Then, click OK.
Now, Excel will record the operations you perform.
Read More: Recording Macro in Excel – Learn with an Example
Step 2: Perform Operation
Now, you can perform the operations you wish. I am just changing the Fill Color of C3:C8.
- Select C3:C8.
- Then, go to the Home
- After that, select the drop-down icon of theme colors.
- Then, choose a color.
- Excel will change the Fill Color.
Step 3: Stop Macro Recording
After performing operations, you need to stop macro recording.
- Go to the Developer
- Select Stop Recording.
Step 4: Run Recorded Macro
Now, it’s time to run the recorded macro.
- Go to the Developer
- Then, select Insert.
- After that, choose a button box.
- Then, draw the box.
- Excel will by default name it Button 1.
- Now, right-click your mouse on the button box.
- After that, select Assign Macro.
- The Assign Macro box will arrive.
- Select Macro_Relative. This is the name of the code I gave earlier.
- Then, click OK.
- Excel will assign the macro to Button 1.
- To run the macro, choose any cell.
- Then, click on Button 1.
- Excel will run the recorded macro.
- Finally, I renamed the button.
The Code:
- You will find the code from the Assign Macro box.
- Select Edit in that box.
- Excel will show you the following code.
Sub Macro_Relative()
'
' Macro_Relative Macro
'
'
ActiveCell.Offset(2, 2).Range("A1:A6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Read More: How to Assign a Macro to a Button in Excel
Use Absolute Referencing in Excel Macro
Now I will show how Absolute Referencing works in Excel Macro and what the difference is between Absolute Referencing and Relative Referencing.
To apply Absolute Referencing, follow the steps.
- Select A1.
- Then, go to the Developer
- After that, select Record Macro.
- The rest is similar to the previous method.
- The new button box is named Absolute Referencing.
- Now, let’s see how Absolute Referencing works.
- Select E7.
- Then, click on Absolute Reference.
- Excel will keep modifying C3:C8 in Absolute Referencing.
Note: That’s the difference between Absolute Referencing and Relative Referencing in Excel Macro. In Relative Referencing, the position changes upon changing references.
- When you set the reference A1, it will modify C3:C8.
- When you set the reference E7, it will modify G9:G14.
But in Absolute Reference, Excel always modifies C3:C8.
- The code for Absolute Reference is this,
Sub Macro_Absolute()
'
' Macro_Absolute Macro
'
'
Range("C3:C8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Reasons for Relative Reference Not Working
Sometimes the Relative Referencing does not work in Excel. One possible reason is that the “Use Relative References” feature is inactive.
The solution is to activate the button from the Developer tab before performing operations.
Conclusion
In this article, I have discussed Excel Macro Relative Reference. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
As a trainer, I often teach the concept of macros. Some of my delegates really struggle with the concept of relative and absolute. I love this example. For those who are new to macros, it’s a great simple way of explaining the differences. Thanks!