Thisworkbook.Names get: Name Value ReferTo Scope Comment...

[drom]

Hi and thanks in advance

When I go in a Excel Workbook to:

• Formulas/Name Manager I can See there the different names:
  • Name
  • Value
  • Refers To
  • Scope
  • Comment

How Can i get for the diferent names in a Workbook

• Name Value ReferTo Scope Comment ???

Dim nm As Name
For Each nm In Thisworkbook.Names
    Debug.Print nm.Name ??
    Debug.Print nm.Value  ??
    Debug.Print nm.RefersToRange.Parent.Name  ??
    Debug.Print nm.Scope ??
    Debug.Print nm.Comment ??
    Debug.Print nm.Visible
    Debug.Print nM.??.
Next nm

I mean how can I get the mainly:

• Name Value ReferTo Scope Comment
• And If I can get something else Better

 

[CephasOz]

Does this meet your needs?

Sub NamedRangeInfo()
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        Debug.Print "Name = " & nm.Name
        Debug.Print "Value = " & nm.Value
        Debug.Print "Parent = " & nm.RefersToRange.Parent.Name
        Debug.Print "Scope = " & NamedRangeScope(nm)
        Debug.Print "Comment = " & NamedRangeComment(nm)
        Debug.Print "Visible = " & IIf(nm.Visible, "True", "False")
        Debug.Print "=================================="
    Next nm
End Sub

' Return the comment of a named range.
Function NamedRangeComment(namToCheck As Name) As String
    NamedRangeComment = vbNullString
    On Error Resume Next
    NamedRangeComment = namToCheck.RefersToRange.Comment.Text
End Function

' Return the scope of a named range.
Function NamedRangeScope(namToCheck As Name) As String
    Dim lngPosn As Long
    lngPosn = InStr(namToCheck.Name, "!")
    If (lngPosn > 0) Then
        NamedRangeScope = Left(namToCheck.Name, lngPosn - 1)
    Else
        NamedRangeScope = "Workbook"
    End If
End Function