Names Object

  • Article
Excel Developer Reference

A collection of all the Name objects in the application or workbook.

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.

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

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.

Visual Basic for Applications 
  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."

Visual Basic for Applications 
  Names.Add Name:="test", RefersTo:="=sheet1!$a$1:$c$20"

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.

Visual Basic for Applications 
  ActiveWorkbook.Names("mySortRange").Delete

See Also