Naming Ranges

You can create a global name that refers to a range using the Add method of the Workbook object's Names collection:

Names.Add Name:="Data", RefersTo:="=Sheet1!$D$10:$D$12"

It is important to include the equals sign in front of the definition and to make the cell references absolute, using the dollar sign ($). Otherwise, the name will refer to an address relative to the cell address that was active when the name was defined. You can omit the worksheet reference if you want the name to refer to the active worksheet:

Names.Add Name:="Data", RefersTo:="=$D$10:$D$12"

If the name already exists, it will be replaced by the new definition.

If you want to create a local name, you can use the following:

Names.Add Name:="Sheet1!Sales", RefersTo:="=Sheet1!$E$10:$E$12"

Alternatively, you can add the name to the Names collection associated with the worksheet, which only includes the names that are local to that worksheet:

Worksheets("Sheet1").Names.Add Name:="Costs", RefersTo:="=Sheet1!$F$10:$F$12"

0 0

Post a comment