|
Home > Macros > Basics > Names Excel Names MacrosUse 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
|
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.
To create a quick list of the defined names (non-hidden) in a workbook, without a macro, follow these steps:

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.
This macro creates a list of names, 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.
Add this code to a regular code module in your workbook, and to run the code, follow these steps
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
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.
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.

Add this code to a regular code module in your workbook, and to run the code, follow these steps
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
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.
Last updated: January 26, 2024 3:16 PM