Names Property

  • Article

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 property as it applies to the Spreadsheet and Workbook objects.

Returns a Names collection that represents all the names in the active workbook. Read-only.

expression.Names

expression   Required. An expression that returns a Spreadsheet or Workbook object.

Names property as it applies to the Worksheet object.

Returns a Names collection that represents all the worksheet-level names that are defined in the specified worksheet. Read-only.

expression.Names

expression   Required. An expression that returns a Worksheet object.

Example

As it applies to the Spreadsheet and Workbook objects.

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