Excel VBA – Named Ranges and Programming
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")
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Did you find this VBA tutorial useful? Then share it with your friends and colleagues: