Using the Name Property of the Range Object

There is a much simpler way to create a name that refers to a Range. You can directly define the Name property of the Range object:

Range("A1:D10").Name = "SalesData"

If you want the name to be local, you can include a worksheet name:

Range("F1:F10").Name = "Sheet1!Staff"

It is generally easier, in code, to work with Range objects in this way than to have to generate the string address of a range, preceded by the equals sign that is required by the RefersTo parameter of the Add method of the Names collection. For example, if you created an object variable rng and want to apply the name Data to it, you need to get the Address property of rng and append it to an =:

Names.Add Name:="Data", RefersTo:="=" & rng.Address

The alternative method is:

rng.Name = "Data"

You cannot completely forget about the Add method, however, because it is the only way to create names that refer to numbers, formulas, and strings.

0 0

Post a comment