Office® XP Development with VBA

Naming Ranges

You can assign meaningful names, such as Sales2001 or IncomeTotal, to ranges in a workbook. Once you assign a name, you can refer to the range by this name. One way to define a named range is with the Range object's Name property:

Dim r As Range
Set r = Range("A2")
r.Name = "MyRange1"

Then, you can refer to the range as follows:

Range("MyRange1").Value = 12

Range names assigned in this way have the limitation that they exist only as long as the code is running and are not saved with the workbook. To create persistent named ranges that will be saved, use the Name object. A workbook has a Names collection containing one Name object for each named range. Use the Add method to define a named range:

ActiveWorkbook.Names.Add(Name ...