Listing defined cell names in VBA window

ezflyer

New Member
Joined
Oct 5, 2008
Messages
10
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,528
Try this macro. You might need to tidy the formatting as the output isn't as pretty as the UI.

VBA Code:
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

New Member
Joined
Oct 5, 2008
Messages
10
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,692
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2008
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,192
Messages
5,570,774
Members
412,340
Latest member
nikitesh95
Top