Names Object

Remarks

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.

Theargument 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 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".

Example

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"

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

ActiveWorkbook.Names("mySortRange").Delete

See also:

• Excel Object Model Reference
• Names Object Members