Make your VBA macros work anywhere: Enable Relative References

Posted on November 18th, 2008 in 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?

Yes!

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:

vba-macro-recorder-relative-feature

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

vba-macro-code-relative-vs-absolute-references

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

  1. Monarch and Excel Make Your Data Work for You says:
    March 18, 2009 at 8:22 am

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

    Reply
  2. CLAUDE BARTAUD says:
    October 15, 2011 at 12:24 pm

    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.
    Sincerely.

    Reply

Leave a Reply