Trouble with the [worksheet].Names-collection!

**uffe** · 05-25-2011, 03:24 PM

Hi all!

I was trouble-shooting one of my (two year old) Excel VBA projects and I bumped into the following: I needed to view all the defined names for each sheet in a workbook. So I typed the following sub

Please Login or Register  to view this content.

This gives me names like 'Diagnos 2'!max_poang. However, I should have gotten 'Diagnos 1'!max_poang too! I know the name is there, since when on the sheet 'Diagnos 1' I type max_poang in the cellref-field (upper left corner), the desired range is selected (on the sheet 'Diagnos 1').

Why then doesn't this name show up when I list all the names in all the worksheets? Can I still reference the range as usual when programming? Anyone knows what's wrong here?

Uffe

**sauerj** · 05-25-2011, 09:51 PM

The problem is that the macro is designed to search for Names that are LOCAL to a sheet and not GLOBAL to the workbook. Previously, it worked because the Names were setup as LOCAL names. Now, your max_poang range name is a GLOBAL range name and the macro, as is, won't work.
If you step thru the macro, you'll find that 'aSheet.Names.Count' is always ZERO for every iteration. That's because, this format (& the 'aSheet.Names(namnNr).Name' text) is designed for names assigned to a specific sheet (a LOCAL name).

If you don't know what I mean by dfferentiating between LOCAL & GLOBAL range names this is understandable, as the MS Range Name editor is so sorely lacking that this almost impossible to discern using MS tools only (

).
To make this understandable & to help you manage these Range Names, I highly suggest that you download & install the following free download. It is extremely powerful in managing Range Names: LINK

The following altered code will pick up all of your range names, but since the names in your file are GLOBAL, you won't see the 'Diagnos 2'! prior to the name as, being GLOBAL, the the names are not assigned to a specific sheet. And, the name will show up repetitively for each & every sheet iteration.

OR, You can use the above download utility to change the names to LOCAL, and then your code will work as you already have it. But, warning (

), if you change a name to LOCAL, then it can only be "seen" within that sheet and not "visible" by the other sheets. Only do this if other sheets don't access this range name.

Please Login or Register  to view this content.

**uffe** · 05-26-2011, 06:03 AM

Please Login or Register  to view this content.

Ok - got it!

I have copies of sheets and I use the same names on each. Perhaps I was not careful enough when defining the names the first time, and that's why some are global and some are local? I guess, from what you wrote, that code will run properly anyway if the reference to the actual sheet is there, since the named range is properly selected when typing for example max_poang - even on the sheet 'Diagnos 1'!

Thanks a lot! I was a bit confused there for a while!