VBA Blogs: Adapting recorded macros – Range Names

Last week, we talked about recording macros to let you repeat and automate sets of commands.  Today, we’re going to look at how you can adapt those recorded macros, without relying on specific cell references.

A useful tip to consider when working in VBA is to ensure that your code doesn’t rely on specific cell references such as “B10” or “C4”, at least, if you don’t want your macros to do things unintentionally.  Let’s look at a typical block of recorded code:

Now, this code works well if no changes are made to the structure of the spreadsheet.  However, if rows or columns are inserted, this will cause errors, since the macro will point to cell “C3” in the first line, irrespective of where the original cell was.  To avoid this, instead of using cell references, we can define cells with Range Names (see here for more details if you need).

By defining code using range names, you ensure that the code will only impact exactly the cells you want it to, unless the range name is deleted.  This gives you much code that will keep working even if columns and rows are added or deleted, as long as the relevant range names still exist.

Hopefully this makes sense!  Next week we will start cleaning up the code and making it look more professional.  We’ll see you then!