Names Property
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