Name Objects and the Names Collections

A Name object represents a defined name for a range of cells. There are two types of names in Excel: built-in names such as Print_Area and custom names created by the user or by code.

Name objects are kept in several Names collections. There is a Names collection for the Application object, as well as Names collections for each Workbook and Worksheet object.

There are a variety of ways to create a new Name object. We can add a Name object to a Names collection by calling the collection's Add method or we can use the CreateNames method of the Range object (discussed in Chapter 19).

For instance, the following code creates a Name object that refers to a range on Sheet1 of Book1. The Name object is added to the workbook's Names collection, but not to Sheet1's Names collection:

Workbooks("Book1.xls").Names.Add Name:="WkBkName" RefersTo:="=Sheet1!$A$1:$B$1"

Note the use of a sheet qualifier in the RefersTo parameter and the specification of an absolute address. If the absolute operator ($) is not used, the range will be defined relative to the active cell.

The following code adds a Name object to the Names collection of Sheet 1 and Sheet2:

Workbooks("Book1.xls").Worksheets("Sheet1") .Names.Add Name:="WkSheet1Name", RefersTo:="=Sheet1!$A$1:$B$1"

Workbooks("Book1.xls").Worksheets("Sheet2"). Names.Add Name:="WkSheet2Name", RefersTo:="=Sheet2!$A$1:$B$1"

Note that this code will also add the Name objects to the workbook's Names collection.

The following code sets the font for the range WkSheet1Name to boldface:

Sheet1.Names("WkSheet1Name").RefersToRange.Font.Bold = True

Note that there is no Names collection for a given Range object, even though a Range object can have more than one name. The best we can do is retrieve the first name for a range object by using the Name property (see the discussion in Chapter 19).

Let us review some of the properties and methods of the Name object:

Delete method

This method, whose syntax is: NameObject.Delete deletes the Name object from the Names collections in which it resides. It does not delete the actual range.

Name property

This property returns or sets the name of the Name object.

RefersTo property

This property returns or sets the formula that defines a named range, in A1-style notation, beginning with an equal sign.

RefersToR1C1 property

This property returns or sets the formula that defines a named range, in R1C1-style notation, beginning with an equal sign.

RefersToRange property

This property returns the Range object referred to by the named range. It is read-only. (See the previous example code.)

Value property

This property also returns or sets the formula that defines a named range, in A1-style notation, beginning with an equal sign. Thus, it is equivalent to the RefersTo property.

Visible property

This property returns or sets the visibility of the named range.

0 0

Post a comment