Contextures Blog

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.
___________