Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Pulling Cell Names into VBA.
Written by Allen Wyatt (last updated April 6, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
If you have used Excel for any length of time, you undoubtedly know that you can define names in your worksheets that refer to various cells and ranges of cells. You can even define names that refer to constants and to formulas. (The naming abilities of Excel are really quite handy.)
As you are developing macros, you may wonder if there is a way to retrieve a list of defined names within a worksheet. This is actually quite easy, if you remember that the defined names are maintained in the Names collection, which belongs to the Workbook object. With this in mind, you can use the following code to put together a variable array that consists of all the names in a workbook:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Dim NamesList()
Dim NumNames As Integer
Dim x As Integer
NumNames = ActiveWorkbook.Names.Count
ReDim NamesList(1 To NumNames)
For x = 1 To NumNames
NamesList(x) = ActiveWorkbook.Names(x).Name
Next x
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5676) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Pulling Cell Names into VBA.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
There are a variety of ways that you might want to count the cells in your worksheet. One way is to figure out how many ...
Discover MoreNeed to run a macro at a given interval? It's easy to do when you learn how to use the .OnTime method, described in this tip.
Discover MoreISBNs are used to uniquely identify books. You may need to know if an ISBN is valid or not, which involves calculating ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-04-10 12:24:50
JimB
THis is interesting, as are so many of your article, but what do you do with this list. I tried it out but it doesn't display anywhere. How do you use this?
2024-04-06 11:37:04
J. Woolley
@Craig Buback
Re. my previous comment below, if RefersTo and Value are both #REF! the defined name can probably be deleted. This does NOT determine if the name is used somewhere.
My Excel Toolbox's NamesInFormulas macro lists hyperlinks to formula cells referencing each visible defined name (named range) in the active workbook. (Hidden names are ignored.) The cell's formula will be included in a comment attached to its hyperlink. The following details are also provided for each name: Scope, Name, Refers To, Value, and Comment. Results are recorded in the active workbook's 'NamesIn...' worksheet. The NamesInFormulas macro does NOT detect names used in charts, conditional formats, data validation, external workbooks, VBA procedures, etc.
See https://sites.google.com/view/MyExcelToolbox/
2024-04-06 11:13:15
J. Woolley
For more information about the named ranges in your workbook, My Excel Toolbox includes the following array function to list defined names with workbook, worksheet, or any scope, including names that are normally hidden:
ListNames([Scope], [SkipHidden], [SkipHeader])
Default value for Scope is null ("") to return all names (any scope).
Default value for both SkipHidden and SkipHeader is False.
This function can be used in VBA to return a base-0 Variant array of information about named ranges in the active workbook:
Dim A() As Variant
A = ListNames(, , True)
Each element of A will be a base-0 array with six elements representing the following Name properties as String values: Scope, Name, Visible, Refers To, Value, Comment.
See https://sites.google.com/view/MyExcelToolbox/
2024-04-06 10:42:27
Craig Buback
What really would be helpful would be a macro that analyses all of your macros and ss,s and determines which names are never used anywhere. Is that even possible?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments