Written by Allen Wyatt (last updated January 28, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Within a macro, Robert would like to figure out the scope of a defined name. For instance, he would like to create a list of all the defined names that are not "Workbook" in scope. He wonders if there is a way to do this programmatically.
Yes, there is. Defined names are stored in the .Name property of the Name object. (Almost sounds redundant, right?) If the name has a scope of a single worksheet, then the worksheet name is included in the .Name property. As an example, consider the following simple macro:
Sub ListNames()
Dim n As Name
Dim sTemp As String
For Each n In Names
sTemp = sTemp & "Name: " & n.Name & vbCr
Next n
MsgBox sTemp
End Sub
If what you see displayed shows a name of something like "Bonus" or "Employees," then the name has a scope of the entire workbook. If the name, instead, is something like "Sheet1!Bonus" or "Sheet7!Employees," then the name has a scope limited to the worksheet included in the name.
This means that you can find all the defined names that are not "Workbook" in scope (as Robert wanted) by modifying the macro just a bit:
Sub ListNames2()
Dim n As Name
Dim sTemp As String
For Each n In Names
If Instr(n.Name, "!") > 0 Then
sTemp = sTemp & "Name: " & n.Name & vbCr
End If
Next n
MsgBox sTemp
End Sub
The examples provided so far rely on stepping through the Names collection, which by default is the collection for the entire workbook. Each worksheet has its own Names collection, however, which means that you could step through the worksheets to determine names:
Sub ListNames3()
Dim n As Name
Dim s As Worksheet
Dim sTemp As String
For Each s In Worksheets
If s.Names.Count > 0 Then
sTemp = sTemp & "Worksheet: " & s.Name & vbCr
For Each n In s.Names
sTemp = sTemp & " Name: " & n.Name & vbCr
Next n
End If
Next s
MsgBox sTemp
End Sub
When you step through the worksheets in this manner, only names with a worksheet-level scope are included. The names still include the worksheet name within them, but names with a workbook-level scope are not visible.
Once you understand that only names at a worksheet-level scope include the exclamation mark, then you can process them in other ways. For instance, you could assign the name to a variable and strip out everything up through the exclamation mark to get the name without the worksheet name. And, interestingly enough, you could change the .Name property from worksheet-scope to workbook-scope by removing the worksheet name.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13007) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Want to replace all commas in a formatted number with periods, and vice-versa? There are a couple of approaches you can ...
Discover MoreWhen processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be ...
Discover MoreImport a bunch of ZIP Codes into Excel, and you may be surprised that any leading zeroes disappear. Here's a handy little ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-02-01 04:32:39
Enno
Thank you Wooley, I overlook this details.
2023-01-31 09:57:09
J. Woolley
@Enno
To list only names with workbook scope, the following statement in ListNames2
If Instr(n.Name, "!") > 0 Then
should be replaced by
If InStrRev(n.Name, "!") = 0 Then
Names with workbook scope will not have "!" so InStrRev will return zero.
2023-01-30 14:22:18
Mechie
It surprises me how many Excel users don't know about Range Names or even if they do, rarely use them. For me, they are one of Excel's great features. (Formulas are soooo much more readable!) I've been using a free Add-In product from Jan Karel Pieterse for many years. It is (surprisingly!) called Name Manager. It taught me a "lot" about range names. You can find it at https://jkp-ads.com/excel-name-manager.asp. Or just do an internet search for JKP Name Manager. One of its many features is a high level Filter that allows one to filter by Name Scope: All; Global; Local to any sheet; Local to active sheet. It has further ability to deal with roughly 20 other classes of names (Eg - With errors, Hidden, Lamda, 2D names, 3D names, Table names, External references and so on. Excel's built in Name Manager has gained some capability over the years, but this free Add-In is still an "on steroids" version.
2023-01-30 11:09:12
Enno
Hi,
I tried to reproduce this tip, but failed.
In both versions the Names with the scope on a special worksheet were displayed, but not that ones with the scope on the whole workbook.
My version of office is 2019.
Greetings
Enno
2023-01-29 10:14:59
J. Woolley
@Ken Kast
Thank you. You are correct. I guess I was thinking ahead when you might want to separate Sheet from Name like this:
k = InStrRev(n.Name, "!")
If k > 0 Then sTemp = sTemp & "Sheet: " & Left(n.Name, (k - 1)) & vbCr _
& " Name: " & Mid(n.Name, (k + 1)) & vbCr
2023-01-28 18:28:25
Ken Kast
J. Wooley,
I don't see why the ! search has to be done from the right end. As you said, only a sheet name can have an !. So when a forward search finds a ! it's either a character in a sheet name or a delimiter. (There may be more than 1, but who cares.) The existence of at least one tells us a sheet name is part of the defined name, hence the scope is worksheet.
2023-01-28 10:45:26
J. Woolley
My Excel Toolbox includes the following dynamic array function to list defined names (named ranges) with workbook, worksheet, or any scope, including names that are normally hidden:
=ListNames([Scope],[SkipHidden],[SkipHeader])
The list includes the following columns: Scope, Name, Visible, Refers To, Value, Comment. When using pre-2021 versions of Excel without support for dynamic arrays, consider UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox
2023-01-28 10:38:33
J. Woolley
The only characters allowed in a defined name are letters, numbers, period, and underscore; however, an exclamation point (!) character is possible in a worksheet's name. Therefore, the following statement in ListNames2
If Instr(n.Name, "!") > 0 Then
should be replaced by
If InStrRev(n.Name, "!") > 0 Then
to search in the reverse direction.
By the way, the VBA function that searches left-to-right is actually spelled InStr(), not Instr().
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