Checking the Scope of a Defined Name

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


8

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining a Value of a Cell

Cells can store all sorts of information that can be formatted and displayed in a myriad of ways. If you want to quickly ...

Discover More

Specifying the Size of Chart Objects

Unhappy with the default size that Excel uses for embedded chart objects? You can't change the size at which they are ...

Discover More

Calculating a Date Five Days before the First Business Day

Excel allows you to perform all sorts of calculations using dates. A good example of this is using a formula to figure ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Deleting Blank Rows

Got some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can ...

Discover More

Error Creating Event Handlers

If you are getting an error when you try to create an event handler, it could be related to a long-known bug in Excel. ...

Discover More

Progression Indicator in a Macro

When your macro is humming along, minding its own business, a user watching the screen may not see any activity and ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

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().


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.