On the View ribbon in Excel, the third option under the Macro section says Use Relative References. This may seem a bit of bewildering technical jargon at first but it makes quite a bit of sense once you get used to it.
First, make sure that the option is switched off (which it will be by default). Excel doesn’t make it massively obvious, unfortunately, as the only indication that the option is enabled is a slightly different icon, which is not the clearest thing to spot:
Having made sure that it is disabled, record a simple macro as follows (see this post for details of recording a macro if you have missed that part):
- Highlight cell A1
- Select Record Macro from the ribbon menu
- Name the macro “Test1”
- Click on cell A10
- Type “Hello”
- Click back on cell A1
- Select Stop Recording from the ribbon menu
Try running this macro (removing the text from cell A10 each time), and you will see that the text appears in A10 and the highlight ends up in A1 no matter where your highlight is when you run the macro. Test this out by clicking on random cells all over the screen and running the macro.
This is because Relative References are disabled, so the macro operates on the specific cells that you define – A10 for the text and A1 highlighted at the end. With Relative References enabled, the macro will operate based on where you are in the workbook at the time you run it.
Enable the Use Relative References option, and then clear all the cells in the worksheet and record the identical macro to the above once again, but this time name it “Test2”.
Once done, place the cursor back in cell A1 if it isn’t there already, and then run the macro – you will see that you get the identical result to the first macro, “Test1”. However, if you then select another cell in the workbook (for example C5) and then run the macro again, you will see that the word “Hello” appears in cell C14 – the same relative position to C5 that A10 is to A1.
This can be both useful and confusing! You need to know whether a macro is going to operate relatively of using fixed references, and there is no easy way of telling before you run it, except by checking the code. However, depending on what you need your macro to do, it’s a useful option to know about.
We’ve covered the basic recording of macros, and all of this can be done with zero programming, and without getting too deeply immersed in anything too technical but, of course, the deeper you go, the more you can do, so if you are up for it, let’s have a look at the next post in the series and get to grips with the code behind the macros!