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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
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,701
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,122,298
Messages
5,595,310
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top