Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Pulling Cell Names into VBA.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
The macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel ...
Discover MoreIf you keep on-going data in a worksheet, some of your data�"over time�"may need to be deleted. If you have an ...
Discover MoreYou can, from within your macros, easily display a message box containing a message of your choice. If you want to ...
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 © 2024 Sharon Parq Associates, Inc.
Comments