Drop the ()'s around the (TmpName) in the .add line and it'll work ok.

Option Explicit
Sub testme()

Dim tmpName As Name
Dim myObj 'variant
Dim myCollection As Collection

Set myCollection = New Collection

For Each tmpName In ActiveSheet.Names
myCollection.Add tmpName
Next tmpName

If myCollection.Count 0 Then
For Each myObj In myCollection
myObj.Delete
Next myObj
End If
End Sub

But why not just drop the collection bit and just delete the name if the
criteria is met?

Tim Richardson wrote:

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.

--

Dave Peterson