Names Collection Object

Description

A collection of all the Name objects in the application or workbook. Each Name object represents a defined name for a range of cells. Names can be either built-in names — such as Database, Print_Area, and Auto_Open — or custom names.

Using the Names Collection

Use the Names property to return the Names collection. The following example creates a list of all the names in the active workbook, plus the addresses they refer to.

Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
For r = 1 To nms.Count
    wks.Cells(r, 2).Value = nms(r).Name
    wks.Cells(r, 3).Value = nms(r).RefersToRange.Address
Next

Use the Add method to create a name and add it to the collection. The following example creates a new name that refers to cells A1:C20 on the worksheet named "Sheet1."

Names.Add Name:="Test", RefersTo:="=Sheet1!$A$1:$C$20"

The RefersTo argument must be specified in A1-style notation, including dollar signs ($) where appropriate. For example, if cell A10 is selected on Sheet1 and you define a name by using the RefersTo argument "=Sheet1!A1:B1," the new name actually refers to cells A10:B10 (because you specified a relative reference). To specify an absolute reference, use "=Sheet1!$A$1:$B$1."

Use Names(index), where index is the name index number or defined name, to return a single Name object. The following example deletes the name "mySortRange" from the active workbook.

ActiveWorkbook.Names("mySortRange").Delete

Properties

Application property, Count property, Creator property, Parent property.

Methods

Add method (Names collection), Item method (Names collection).