Range Name definition wonky (Excel 2000)

[ChrisGreaves]

I have used the Excel 2000 menu sequence Insert, Name, Define (select "Admin") in both sheets in the attached workbook.

When I'm in sheet 20040605, I'm told that Admin is cell B6 in 20040605.
When I'm in sheet Sheet1, I'm told that Admin is cell B8 in Sheet1.

I can't reconcile having two definitions for the one range name in two worksheets in one workbook, and would appreciate an explanation.

[HansV]

A defined name can be global to the entire workbook, or local to a worksheet. Your workbook contains two names 'Admin': one global to the workbook, the other local to the worksheet 20040605. Here is how Excel 2007 lists them:

x152.png

When 20040506 is the active sheet, the local definition has precedence, so you 'see' the one referring to ='20040605'!$B$6.
But when any other sheet is active, you 'see' the global name referring to =Sheet1!$B$8.

As you see in the screenshot, there are also two versions of the name 'Days', although the local one is not valid any more.

[HansV]

By the way, I recommend downloading and installing Jan Karel Pieterse's excellent free Name Manager add-in. It offers a much better way to view and manage defined names than the built-in menu options in Excel 2000.

[ChrisGreaves]

A defined name can be global to the entire workbook, or local to a worksheet.

Thanks Hans.
I half-suspected Global vs. Local but cannot recall having ever heard of it. In the back of my mind was a greater possibility that the workbook was corrupted.

I recommend downloading and installing Jan Karel Pieterse's excellent free Name Manager add-in.

Done.
Thanks.
And "Thank You" to Jan Karel, too.

[rory]

I take it the 'Excel 2008' is a typo then?

[HansV]

I have changed Excel 2008 to Excel 2000 in the subject.

[ChrisGreaves]

I take it the 'Excel 2008' is a typo then?

Thanks Rory and Hans. (avoids use of line "Many hans make post work).

[ChrisGreaves]

A defined name can be global to the entire workbook, or local to a worksheet.

It just goes to show how short short-term memory really is.
The day before yesterday I had to move a defined cell "Currency" to another worksheet in the same workbook, and my VBA code

Code: Select all

strCurrencySymbol = Range("Currency").Value

rendered a run-time error '1004' Method 'Range' of object 'Worksheet' failed.
I resolved the problem (after several minutes head-scratching) with this

Code: Select all

strCurrencySymbol = Worksheets("Calculations").Range("Currency").Value

Yesterday morning I posted my question to start this thread oblivious to yesterday's lesson that the Range was LOCAL to the worksheet("Calculations") to which I had moved it.
I only remembered tonight because I have just made the same mistake (move a defined cell "Currency" to another worksheet in the same workbook) on the next workbook in the set.

(P.S. Please, can we have a :dunce: smiley?)

[Jan Karel Pieterse]

Just a quick note:
Try to avoid having both a local name and a global name with the same name, they can lead to problems in your file.

[ChrisGreaves]

Try to avoid having both a local name and a global name with the same name,

Exactly.
I think I got myself into this mess by
* receiving the workbook,
* naming the original cell e.g. "admin",
* then copying the cell to a different sheet.
* naming the new cell "admin" and
* deleting the old cell,
* believing that Excel would automatically remove the name definition from the old sheet and re-assign it to the new sheet.
* My lack of awareness of local and global names led me to think that a name was a property of a workBOOK rather than a workSHEET.
With that Belief in place I was headed for a fall.
Thanks, Jan Karel.

[ChrisGreaves]

Try to avoid having both a local name and a global name with the same name,

Jan Karel, I've given this some thought.
Based on my experience and your advice, it's a good idea, but i have difficulty reconciling it with all my programming experience.
We habitually make use of global and local names in VBA all the way back to FORTRAN (and beyond).
There is no philosophical reason to deny an Excel programmer (using range names in worksheets) from wanting to use the same name for two different areas of memory; we do it all the time in VBA.
The reason for suggesting avoidance then has to be on a more practical basis, and I suspect that is the awkwardness of implementation of local/global scope of range names in Excel.
There is nothing apparent in the Excel user interface that suggests to a use that a name is being made local or global.
I saw nothing in my examination that was a direct indication of scope - it was the jump from $B$6 to $B$8 that caught my eye.
Also I see nothing that sticks out when I look at a range or range name in VBA.

The reason for avoiding duplicate range names has to be the failure of Excel to provide for their management, rather than any proper programming practice.
Do you agree?

(later) The KB article has since provided me with more insight.

[Jan Karel Pieterse]

I partly agree. The indication of scope of a name is visible, but not apparent. The Insert, Name, Define dialog does show whether a name is global or local; it shows the sheet name next to the name foe local names. In Excel 2007, it shows the scope of the name in the dialog as well.

I too use differently scoped names in VBA, but I always have a prefix indicating scope. In effect, the variable names are thus not spelled equally and can be discerned.

There are pitfalls using duplicate global/local names. VBA is picky with global/local names. If you try to programmatically delete or change a global name when a worksheet on which an identical local name is defined is active, then your changes are made to the local name, not to the global name (!). This is a bug of course, but can't be helped and must be taken into account.

[ChrisGreaves]

VBA is picky with global/local names. ... This is a bug of course, but can't be helped and must be taken into account.

Jan Karel, thank you for the confirmation. I am poised to rewrite all of my range-handling code and while it is to some extent a mechanical process, since the utility is geared towards "cleaning up workbooks" I wanted to decide whether to use the local/global conundrum as an alert or caution. That Excel (and/or Excel/VBA) is buggy in its handling tells me that a rewrite of my simple code is necessary, and I shall go ahead. That global/local names are perceivably of value tells me that users ought to be able to use them with impunity.
The classic example would be the consolidation of congruent worksheets from various 'departments' of an organization.
Thanks again.

[StuartR]

...The classic example would be the consolidation of congruent worksheets from various 'departments' of an organization.
Thanks again.

Another example would be if you have a template sheet, that is copied multiple times for each "thing" that the workbook describes.

[ChrisGreaves]

Another example would be if you have a template sheet, that is copied multiple times for each "thing" that the workbook describes.

Quite so, and thanks, Stuart, for a supporting example.
The whole business (end-user and VBA programmer) of Local/Global names starts to look like another piece of MSOffice that was developed in a separate building (without telephones) on campus, or was shipped out when 95% complete.

[ChrisGreaves]

The whole business (end-user and VBA programmer) of Local/Global names ...

I use a home-grown chunk of code to name ranges. My current thinking is that I'll do myself a favor and preface each range name with an abbreviation of the range's sheet name.
My reasoning is that I can keep each name unique and hence local to the sheet, and that reading formulae will be enhanced since the range name will continue to refer to a sheet.

[Jan Karel Pieterse]

But first consider if a range name is always tied to that sheet, if so, it may be easier to make it local. That also avoids duplicate local/global names to surface when you copy the sheet itself, or the range with the name to a different sheet.