Names Collection

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