Scope of a name

[cecil]

How do I tell the scope of a name in code

Here is what I did. I am just wondering if there is a better way?

Code: Select all

If TypeName(nme.Parent) = "Worksheet" Then
                'do something 
            ElseIf TypeName(nme.Parent) = "Workbook" Then
                'do something else
            End If

[Rick Rothstein]

Some people prefer using Select Case for the structure you have (it sort of looks "cleaner")...

Code: Select all

Select Case TypeName(nme.Parent)
  Case "Worksheet"
    ' do something
  Case "Workbook"
    ' do something else
  Case Else
    ' an optional "catch all" for if none of the above Case statements get triggered
End Select

[HansV]

That is the correct way to do it, only you don't need to check both conditions - since the scope is either worksheet or workbook, you can simplify the code to

Code: Select all

    If TypeName(mne.Parent) = "Worksheet" Then
        ' local
    Else
        ' global
    End If

[Jan Karel Pieterse]

Note that there are problems when a name has both local and global copies.
If the active sheet is a worksheet with a local copy and you are trying to change the global range name through code, then the local range name will be changed.
Example.
Sheet1 has a global name Test pointing to Sheet1!$A$1
Sheet2 has local name Sheet2!Test pointing to Sheet2!$A$1

Select sheet1
Run this (from the immediate window for example):
ThisWorkbook.Names("Test").Delete
The global name is deleted.

Define a global name Test again, pointing to sheet1!$A$1
Select Sheet2
Run the same command. Expect to see the global name Test disappear. But instead, the LOCAL name is deleted.

[cecil]

Jan Karel:

That is my problem. Someone, back in Excel 95 or so, created names with Global and local scope with a common identifier. There are 85 workbooks with approx 2500 formula and 600 different names in the model. I am writing code to determine what names are used/not used and where. Sometimes my code works and sometimes not. I will play with the info you have provided and deactivate sheets as necessary.

Basically the code I am using is

for each nme in workbooks("some_workbook").names
nme.name = "new name"
or
nme.delete
next

[Jan Karel Pieterse]

I advise you to download my Name Manager. It has a feature to check for unused names and is quite good at handling local/global names.

[rory]

Seems to be more complicated than that to me (in XL2003 anyway). If the first sheet in the workbook has a local copy of the name, then that name is deleted regardless of which sheet is active.

[cecil]

Thanks Rory. If this is the case, I am in luck. I will change my test model to look more like the real model. I was testing with two workbooks, 6 names and a couple of formula looking at 2 names. All the names were on the first two sheets. In the real model, the first sheet is a title page with no names.

Most likely I will not get back to this until Monday, but you folks have been a great help. Thanks again to all.

[Jan Karel Pieterse]

Hi Rory,

Indeed. I had forgotten about that one. Name Manager inserts a new temporary sheet at the beginning of the workbook to work around that.