Public Sub List_Defined_Names()
Dim dn As Name, dnValue As String
Dim p As Long
Dim cell As Range
Debug.Print "Name", "Value", "Refers To", "Scope", "Comment"
For Each dn In ActiveWorkbook.Names
If dn.RefersToRange.Count = 1 Then
dnValue = dn.RefersToRange.Value
Else
dnValue = "{"
For Each cell In dn.RefersToRange
dnValue = dnValue & Chr(34) & cell.Value & Chr(34) & ";"
Next
dnValue = Left(dnValue, Len(dnValue) - 1) & "}"
End If
p = InStr(dn.Name, "!")
If p = 0 Then
Debug.Print dn.Name, dnValue, dn.RefersTo, "Workbook", dn.Comment
Else
Debug.Print Mid(dn.Name, p + 1), dnValue, dn.RefersTo, Left(dn.Name, p - 1), dn.Comment
End If
Next
End Sub