Excel VBA – Named Ranges and Programming

Associated Files Download Links

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:

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:

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!

alt text

Learn More!

<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:

You may also like some of this related content...

We have already gone over what variables and constants are, in our VBA Data Types
Here is example code to determine the Start and End Column of a Merged Cell.
If you need to get the active cell's column or row from VBA, use this
Worksheet_Change Event You may want to run a macro when a cell changes. A popular
Select All Cells In Worksheet with .Cells To select all the cells on a sheet