Home > Macros > Basics > Names Excel Names Macros Use macros to work with Excel names. Create a quick list of non-hidden defined names, or list all non-hidden names, with details, to see where each range is located

Introduction

If an Excel workbook contains defined names, you can go to Name Manager on the Formulas tab, to see a list of those names.

For troubleshooting, or for documenting the contents of an Excel workbook, it can be helpful to create a list of those names, on a worksheet.

• For a quick list of defined names, sekect a blank cell, then run the QuickNamesList macro
• For a detailed list of defined names, use the ListAllNames macro.

Quick List of Names - No Macro

To create a quick list of the defined names (non-hidden) in a workbook, without a macro, follow these steps:

1. Select a cell in a blank part of a worksheet
2. On the keyboard, press F3, to open the Paste Name box
3. Click the Paste List button

A list of names is added to the sheet, starting in the selected cell. The first column shows the defined name, and the second column shows the Refers To formula.

Widen the columns, if necessary, to see the full names and Refers To formulas

NOTE: Paste List only lists workbook scope names, and worksheet scope names for the active sheet.

Quick Names List Macro

This macro creates a list of macros, starting in the selected cell on the worksheet, using Excel's built-in command -- Paste List.

At the start of the macro, the code checks the selected cell, to see if it is empty. If it is not empty, you'll see a message, asking if you want to create the list.

By default, the "No" button is selected, so you can press the Enter key to stop the macro from continuing.

NOTE: The ListNames method, like the Paste List command, only lists non-hidden names, with workbook scope, and worksheet scope names for the active sheet.

QuickNamesList Macro Code

Add this code to a regular code module in your workbook, and to run the code, follow these steps

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select the QuickNamesList macro in the list, and click the Run button

Sub QuickNamesList()
Dim lRsp As Long

If Selection <> "" Then
lRsp = MsgBox("Selected cell is not empty." _
    & vbCrLf _
    & "Create list here?", _
      vbYesNo + vbCritical + vbDefaultButton2, _
      "Cell Not Empty")
  If lRsp <> vbYes Then Exit Sub
End If

Selection.ListNames

End Sub

List All Names Macro

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)

NOTE: This macro will list names with a worksheet scope, even if that sheet is not active. The built-in Paste List only list workbook scope names, and worksheet scope names for the active sheet.

ListAllNames Macro Code

Add this code to a regular code module in your workbook, and to run the code, follow these steps

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. Select the ListAllNames macro in the list, and click the Run button

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 Workbook

To test the macros from this tutorial, download the zipped Excel Names Macros sample file. The workbook contains the macros from this page, and a few defined names for testing.

The zipped file is in xlsm format, and be sure to enable macros when you open the workbook, if you want to test the macro code.