Excel VBA – Named Ranges and Programming

Named Ranges

Using named ranges when programming references to cells can save you time and rework effort as your spreadsheet requirements change.

When I first started coding in Excel I hard coded each reference to a cell. For example, each time I would reference or set a property of the Cell A2 on Sheet1 I would use something like this:

varProductID = Sheet1.Range("A2")

Big problems. The spreadsheet would work as desired, however anytime an employee inserted a row at the top of the spreadsheet, all of the code fails. This can add up if you write to that Cell, Read from that Cell, and change the properties of that Cell often from code.

So I got smarter and started declaring all of the major cells I needed to reference as variables at the top of my code. Now anytime a Cell that is referenced from code moved, I could simply change the reference in one place in my code and have it work for every reference.

Better, but still not perfect. There is still a need for me to be called to manipulate code if the spreadsheet changes, even if it’s only in one or two places.

The solution: Named Ranges

I define a Cell A2 with a named range, and reference the Name from code. Now an employee can insert rows, or cut an paste A2 to their hearts desire, and the code still works. An example:

varProductID = Sheet1.Range("nrProductID")