When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
Let’s take a simple example where we record a macro to select an input cell B2 on a sheet named Input and enter a 1.
Recorded code is hardly ever efficient or robust. In the example above changing the sheet name or inserting a row or column could break the macro.
To fix it we should use the sheet code name, and we can also get rid of the select statements and use a range name to refer to the cell.
In fact a one row command does it all.
Sheet1.Range("MonthNumber").FormulaR1C1 = "1"
The sheet codename (Sheet1) is the one shown in the VBA window – see image below. It is not the tab name and it isn’t easily changed by the user. This code includes a range name.
Recorded macros select things, but in most cases you don’t need to select anything to change it.
I have created a range name MonthNumber for cell B2 on the Input sheet see below.
To create a range name select the cell/range and then click inside the Name Box (left of the Formula bar) and type the name and press Enter.
Once the macro code uses the range name then it handles inserted/deleted rows and columns.
Range names can’t include a space – you can use the underscore character. You also can’t include any names that are also a cell references e.g. Q1 or QTR1 are both cell references in Excel. You could use Q_1 or QTR_1.
To refer to range names in code there is even a shorter technique.
[MonthNumber].FormulaR1C1 = "1"
Whilst the code is shorter it does take a fraction longer to run. This is not an issue for most macros. It is also quicker to type as no quotation marks are required around the name when you use the square brackets.