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 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.
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.
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.
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 25, 2023 2:54 PM