Names Collection Object

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