"That name is not valid"

[Nile_Hef]

How does Excel enumerate the Names collection?

We're getting spreadsheets from overseas offices with named ranges that we
cannot access by name, nor delete, rename or hide... Not even when I
enumerate the names collection, or loop though it by ordinal number:

For each MyName in Thisworkbook.Names
MyName.delete
Next MyName

or the alternative:

For i = Thisworkbook.Names.Count to 1 Step - 1
Thisworkbook.Names(i).Delete
Next i


As soon as either loop hits the offending item in the collection, VBA raises
the error "That name is not valid".

Inspecting the names collection using the 'Insert; Names...' dialog from the
worksheet menubar reveals that the name contains non-standard characters
which display as odd squiggles and squares on a UK- or US-locale PC. Possibly
they are umlauts, accented vowels, or copied from Cyrillic documents.

Changing the locale to Sweden, Germany or Japan doesn't help. (Japan was a
wild guess - we know it's the wrong language, but the codepage is so large
that surely there's no such thing as an invalid character...).

All suggestions welcome*

Also... Some idea of how Excel actually stores that collection would be
interesting. Surely it's got some kind of internal ordinal; I can't believe
that it's indexed by hashing the unicode string from the name.


* The suggection 'Nuke the site from orbit' has been carefully considered
but was found to be impracticable for reasons of health & safety.

 

[Dave Peterson]

How about a test when you're trying to manually delete that name...

Tools|Options|general tab|check R1C1 Reference style.

See if you get a prompt stating that you have an invalid name and it has to be
renamed. If you do name it something that's ok (deleteme01). Then delete that
name and go back to A1 reference style.

ps.

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.

In fact, I think that this utility has the ability to fix these invalid names.

 

[Nile_Hef]

Thanks, I'll try that in the morning. I wouldn't expect the reference style
to solve the problem, as it's almost certainly due to invalid characters in
the name, rather than in the address, but I I've learned over the years that
not all fixes work for the reasons you think they ought to work.

Names manager is a suggestion I ought to have tried!

Thanks.

N

 

[Dave Peterson]

Try manually toggling that setting first.

In fact, make a copy of the workbook with the bad name. You may want to
experiment later on. And if you fix the bad name, you may not have any good
data to test.

 

[Nile_Hef]

Dave -

Toggling that setting has actually worked. The user most affected by this
issue has been shown how to do this, and now has a copy of the Names Manager;
as some of the sheets he gets contain over three thousand names - most of
which refer to remote files behind the senders' firewalls - he is happy and
will probably make contented burbling noises for the remainder of the
afternoon.

Thanks for the help -

N

 

[Dave Peterson]

Excel has to look at names when you toggle that setting.

If you're in R1C1 reference style, then X88 is a valid name. Switch to A1
reference style and X88 isn't valid. It looks like an address.

And you're just piggybacking on that work that excel does.