Names Collection

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Names Name

A collection of all the Name objects in the workbook. Each Name object can represent a defined name for a range of cells, a formula, or a constants value.

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, along with the addresses to which they refer.

  Sub List_All_Names()
   Dim nmCurrentName
   Dim rngCurrent

   Set rngCurrent = Spreadsheet1.ActiveSheet.Range("A1")

   ' Loop through all of the names in the active workbook.
   For Each nmCurrentName In Spreadsheet1.ActiveWorkbook.Names

      ' Write the current name to the worksheet.
      rngCurrent.Value = nmCurrentName.Name

      ' Write the definition of the current name to the worksheet.
      rngCurrent.Offset(0, 1).Value = "'" & nmCurrentName.RefersTo

      Set rngCurrent = rngCurrent.Offset(1, 0)
   Next
End Sub

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

Spreadsheet1.Names.Add "CurrentMonth", "=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".