Make your VBA macros work anywhere: Enable Relative References

Learn Excel - 2 comments

Do you know that while recording macros in excel you can use the relative reference based recording so that you can use your macro anywhere and the references would be adjusted automatically?


I have learned this yesterday and I thought it is pretty cool.

Here is how you can make your macro VBA code references relative:

When you select “record new macro” from menu > tools > macros, excel shows a very little macro recording toolbar with a start button and another button. That another button is the one we use to toggle between relative and absolute references in macro code.

See this:


And the code generated by relative and absolute references is shown here:



Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

2 Responses to “Make your VBA macros work anywhere: Enable Relative References”

  1. [...] Chandoo isn’t adverse to recommending VBA solutions either. [...]


    Thank you for the informations you give.
    I am using Excel 2003. The button absolute/relative is no more operating. Once I have set relative reference macro code I can't go back to the default option for all my Excel files.
    I'm looking either a method to reinitialize Excel or a VBA instruction to force the absolute option.

Leave a Reply

« »