|
|
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
NextUse 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