You probably use defined names in some of your Excel workbooks. We’ll look at a built-in way to list the names in a workbook, and see some Excel VBA code that creates a more detailed list of names.
Using Names in Excel
You can name a group of cells, and use that name as the source for a data validation drop down list.
For example, if you have entered a list of the products that you sell, you could select the list, and name the range reference as ProdList.
Then, that product list could be used in an order form.
Table Names
If you created Excel Tables, in Excel 2007 or Excel 2010, they are automatically named.
Later, you can change the names to something meaningful, such as ProdTable, for a list of products and their prices.
Create a List of Names
If you’re working on a complex Excel workbook, it’s easy to lose track of what you’ve named, and where the named ranges are located.
For reference, you can print out a list of names, using a built-in feature in Excel.
To paste a list of workbook level names in Excel:
- Insert a blank worksheet
- On the Excel Ribbon, click the Formulas tab
- In the Defined Names group, click Use in Formula, and click Paste Names (the keyboard shortcut is F3)
Paste Name Dialog Box
Next, in the Paste Name window, click the Paste List button.
Names List on Worksheet
A list of defined names and their formulas is pasted into the worksheet.
Note: To see worksheet level names, use the Paste List feature on the worksheet where those names are defined.
Create Names List with Excel VBA Macro
The built-in names list feature is helpful, but if you need more details, you can create your own list, by using Excel VBA.
This macro adds a new sheet to the active workbook, with a list of the non-hidden defined names, with details for each name, if available.
- A – Name;
- B – Refers To formula;
- C – Number of cells in the range;
- D – Sheet name where range is located;
- E – Address on worksheet;
- F – Scope (workbook or worksheet)
Sub ListAllNames() Dim lRow As Long Dim nm As Name Dim wb As Workbook Dim ws As Worksheet Dim wsL As Worksheet Dim wsName As String Dim shName As String Dim myName As String Dim nmRef As String Dim nmAddr As String Dim nmRng As Range Dim nmSc As String Dim lCells As Long Set wb = ActiveWorkbook Set ws = ActiveSheet Set wsL = Worksheets.Add wsName = ws.Name With wsL .Range("A1:F1").Value = Array("Name", _ "Refers To", "Cells", "Sheet", "Address", "Scope") lRow = 2 End With On Error Resume Next For Each nm In wb.Names If nm.Visible Then Set nmRng = nm.RefersToRange myName = nm.Name nmRef = "'" & nm.RefersTo lCells = nmRng.Cells.Count shName = nm.RefersToRange.Parent.Name nmAddr = nm.RefersToRange.Address If TypeOf nm.Parent Is Workbook Then nmSc = "Wb" Else nmSc = "Ws" End If wsL.Range(wsL.Cells(lRow, 1), wsL.Cells(lRow, 6)).Value _ = Array(myName, nmRef, lCells, shName, nmAddr, nmSc) lRow = lRow + 1 Set nmRng = Nothing myName = "" nmRef = "" lCells = 0 shName = "" nmAddr = "" nmSc = "" End If Next nm With wsL .Rows("1:1").Font.Bold = True .Columns("A:F").EntireColumn.AutoFit End With End Sub
Download the Names List Sample File
To get the sample workbook, and the Names List code, go to the Excel Names Macros page on my Contextures site.
The file is zipped, and in Excel xlsm file format, and it contains macros.
___________
I didn’t realize you could simply paste a list of names. Thanks for that tip.
Just wanted to clarify that, even though this page talks about naming Excel Tables – that you can change the names to something meaningful, such as ProdTable – Table Names are not included in the built-in tool in Excel that lists Names (even though tables are listed in the Name Box to the left of the Insert Function and Formula Bar boxes). Neither are Table Names included in The Names List Code.
This is not to imply that the page doesn’t do everything it’s advertised to. I was just hoping to find code about listing Table Names too. I’ll keep looking.
@Glenn, there is sample code on my website that will create a list of tables:
http://www.contextures.com/xlExcelTable01.html#list
@Debra That’s awesome – thanks for posting that! Similar to what I ended up writing, but I didn’t think to check if ListObjects existed in the worksheet. I’ll add this condition to my code:
If ws.ListObjects.Count > 0 Then
I was hoping you can help me, we are trying to find a way to track Parent and the child’s name for things like early dismissal, Tardy, Visiting, or Volunteering and then at the end of the month running a report to see how many times they were late, left early, visited or Volunteered. Is there away to do this in excel??
I just uploaded a sample file that might help you, on my Excel Sample Files page, in the Pivot Table section:
PT0043 – Track and Summarize Activities
http://www.contextures.com/excelfiles.html#PT0043