VBA TUTOR NEWSLETTER ~ TUTORIAL SIX: Using named Ranges in Excel

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 5 >Tutorial 7

VBA TUTOR NEWSLETTER ~ TUTORIAL SIX:
Using named Ranges in Excel

In this tutorial (of sorts) we will look at how to name ranges correctly to avoid problems with local and global scope of the named range, then go on to look at referencing named ranges in your VBA code.

Rule number ONE is to ensure the sheet name is included in the name of a range for it to have local scope in the 'Refers To' section, and to ensure that there is no sheet name included in the name of a range for it to have global scope in the 'Refers To' section.
Rule number TWO is to NEVER use the same name for a global and a local range name, as Excel will always use the global name, overriding the local name.

Follow these two rules and you should have no problems using named ranges in Excel.

Download an excellent workbook that demonstrates the use of named ranges and formulas at www.cpearson.com/excel/named.htm, making sure you read the excellent online description as well.

Finally, for a very good explanation of globally named ranges, go to http://www.zdjournals.com.

Referencing named ranges in VBA

We all know how to use formulas in EXCEL. So, the easiest way to use named ranges is use the 'formula' property to set a formula containing references to named ranges to a cell, than access the value of that cell.

Worksheets("Sheet1").Range("Z1").Formula = "=SUM(indexrange)"
Worksheets("Sheet1").Range("E5").Value=Worksheets("Sheet1").Range("Z1").Value

Elegant programming? No! But for the beginner to intermediate VBA programmer, it allows you to use the knowledge you already have with formulas in VBA code.

Have a look at named_ranges.xls, a simple demonstration of using this technique with named ranges.

As you investigate further, you will find that using named ranges, as well as named formulas, greatly increases the power of Excel.

OTHER RESOURCES for RANGES:

[1] Download a 'Dynamic Range Wizard'.
[2] A selection of great functions that can be applied to ranges.

HOPEFULLY, THESE CODE EXAMPLES WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!