Excel 2000.


I am writing a macro to process Names. As I go through the processing,
I want to add various names to a Collection. After processing the
Names, I want to iterate over the Collection of Names I made, and
delete them. But it is not working, because the Name objects are not
added to the Collection, only a string copy of the name of the Name.
This is definitely not what the documentation says should happen.

so the code looks like this:


dim tmpName as Name
dim myCollection as new Collection

for each tmpName in activeSheet.names
if <... then
... processing (I am adding a new Name based on the the name of
some existing Names)
... myCollection.add(tmpName) 'to keep track of names I want to
delete
end if

next tmpName

dim myObj 'variant
for each myObj in myCollection
myObj.delete
next myObj

BUT the collection does not collect Name objects! It collects strings
(the name of the Name). So therefore the delete does not work.

So I don't understand: tmpName is a Name according to the debugger, but
when it is added to the Collection, it gets converted to a string
representation of the name of the Name. Therefore the Collection is
useless.