Naming an Excel.Range

Although there are several different ways to name ranges, the simplest and the most efficient is to:

1.       Select a range

2.       Define a name : worksheet level or entire workbook level using the name property of the range object

How to name a range

Named ranges are part of the Workbook’s names collection and support two levels of names: worksheet  and  workbook.  After selecting a range, the next step is to name the range. To make a workbook level name,  just include the name in the name property of the selected range object.  Making a worksheet level name is a little more complex as the range’s name property includes the worksheet name plus the exclamation point and the desired name.   Check out these examples for clarity.

Example	Note
Range("A1:E5").Name="Header2"	Workbook name
Range("A1:A4").Name="Sheet1!Col_team2"	Worksheet specific name

 

How to use ranges with names

The names that you created – either workbook or worksheet specific are added to the workbook’s names collection.  Again- since this is Excel – there are various ways to refer to ranges by names but the most flexible is to use the RefersToRange property of the Name object.  To retrieve the value of the first cell in the Workbook level name Header2 try: Names("Header2").RefersToRange.Cells(1,1).value.  The main advantages of referring to ranges by names instead of by their cell address  are:

·         Transparency in formula design

·         Flexibility

·         Maintenance free