Hello, Excellers. Welcome to another #MacroMonday for more Macro and coding fun. Today, let’s look at a handy topic. First, I will show you the difference between recording an Excel macro with an absolute versus a relative reference.
Generally, Excel will record the EXACT references to the selected cells if you use the Macro Recorder in Excel. Here is an example. I have recorded a Macro the turn cells A1:A10 yellow on my Excel worksheet.
The code that has been generated from the Macro Recorder can be seen below.
If I run the Macro, then the cells in A1:A10 will turn yellow as expected.
Adter that, if I then try to re use this Macro on another range of cells, say D1:D5 does it work?. No it does not work.
Only the absolute cells referenced in the macro are affected, as Excel carries out the EXACT instructions. But, by instructing Excel to record the macro with relative referencing, we get a different and way more helpful outcome.
Record A Macro Using Relative Referencing.
Before you begin to record a macro with relative referencing, we need to tell Excel to use relative referencing.
- Developer Tab
- Select Use Relative References
- Record your Macro
- Click Use Relative References again to turn off this function
I have recorded the same Macro as in the above example. But, I have used Relative Referencing. Here my code this time.
You can see that the code is slightly different and Excel will keep track of the Relative Reference. This means the macro will affect those cells relative to where your cursor is positioned when you invoke the macro.
Let’s test it then!!!!! It works! wherever I put my cursor the cells 1 to 10 are changed to yellow.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
