Listing defined cell names in VBA window

[ezflyer]

Is there a way to display the defined cell names for the active workbook in the VBA window? I have a project with a large number of defined cell names and it would be great to be able to look up those names without going to the Name Manager for that worksheet

 

[John_w]

Try this macro. You might need to tidy the formatting as the output isn't as pretty as the UI.

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

 

[ezflyer]

Hi John:

Thanks for the suggestion. However, I don't think I explained myself properly. What I am looking for would be something similar to the List Properties/Methods command, but instead of showing the properties and methods available for an object, I would like it to show me all the defined cells I have in my workbook and allow me to add it without having to look it up and worry about syntax. I can't find this anywhere and it seems like it would be a useful feature.

Thanks again

Bill

 

[Trevor G]

Bill why not use the Inquire Tab it can analyse your workbook, within the Range options you can get to provide the Named Ranges and also identify the cells they refer to. Inquire is a tab you have to tell excel to display through the Custom Tab options

 

[ezflyer]

Thanks for the suggestion. I couldn't find the tab and when I checked with Microsoft Support I was told it isn't available with Office 365 for Families, the version I have. The cheapest version of 365 for Business is about twice the price so I need to see what else it offers to see if it is worthwhile.