If you’re new to Excel macros, you’ve probably discovered both how helpful they can be, and how frustrating they can be. This is the result of Excel trying to understanding what you’re doing, and not always getting it right.
Here’s an example. Let’s say that you want to enter “ABC Company” in cell A1, and then “Expense Report” in cell A2. You create a new Excel worksheet and start the macro recorder. Since your cursor is already positioned in cell A1, you type “ABC Company”. You then press Enter to move to cell A2, and type “Expense Report”. You press enter to complete the entry, and then stop recording.
All is well as long as you follow the same steps every time. If you now create a new worksheet and run your macro, it should work just fine. But if you create the new worksheet, then click on cell C3 before you run your macro, it’ll type “ABC Company” in C3. The “Expense Report” is will either end up in cell C4 or in cell A2, depending on a thing called Relative References.
When you were recording your macro, there were two ways Excel could possibly have interpreted your actions:
After you typed “ABC Company” and pressed Enter, your cursor moved to cell A2. Excel may have interpreted that as “Move to the next cell down”. This is called a relative reference. That means that when the macro is played back, it’ll always move down to the next cell before typing in “Expense Report”. If your cursor is positioned on cell C3, for example, it’ll move down to cell C4 before entering the text.
The alternative is that Excel may have interpreted your actions to mean that you always wanted to move to cell A2, regardless of your previous position. This is called an absolute reference. In this case, it doesn’t matter what cell is active when the “ABC Company” is entered, the macro will always move to cell A2 before entering the “Expense Report” text.
If you’re not clear on that, stay with me. In a minute, I’ll give you examples.
First, though, let’s learn how we control this.
In Excel, click on your “View” ribbon, and then click the little drop-down arrow next to the “Macros” button:
Notice the bottom item: Use Relative References. This controls how Excel will interpret your actions in the scenario we’re discussing. In my screen shot, the Relative References is turned off. That means I’m using absolute references. In this case, my “Expense Report” text will always end up in cell A2, no matter where my cursor is when I run the macro. Here’s the result of running the macro with this setting, when my cursor starts out in cell C3:
As you can see, it didn’t matter that my cursor started in cell C3. The “Expense Report” text appeared in cell A2 anyways. In fact, I could have started with my cursor in any cell, and the “Expense Report” text would have ended up in cell A2.
Next, I’ll re-record the macro with the “Relative References” turned on. Note that it’s not easy to see when this button is turned on. When I click it, mine looks like this:
See the very small gray border around the button? That means it’s turned on. I wish Microsoft had made this a little more obvious. Just to be extra clear, here are the two states of this button:
Subtle, right? You’ll have to look closely at this one to be sure which mode you’re using.
Now, let’s see what that macro does when I record it with the “Use Relative References” feature turned on:
As you can see, Excel has interpreted my actions to mean, “After typing ABC Company, move down 1 cell and type “Expense Report”.
If you’re not quite clear on this concept, I suggest you try it a couple of times, toggling this “Use Relative References” between recordings. It may take a little time and practice to master this feature, but I expect it’ll be well worth your time. This feature is key to making your macros work properly, and understanding it will save you a lot of frustration.
Of course, as always, reach out to me if you need help with your macros. I'm a total macro geek, and would love to get my hands on yours!
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2022. All rights reserved.