Adding Name object to a collection does not add an object

[Tim Richardson]

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]

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?

 

[Peter T]

Not sure what you mean by the debugger but might be returning the default
property of the name, ie a string. Shouldn't be any problem to add Name
objects to your collection -

Sub Test()
Dim col As Collection ' normally at module level
Dim nm As Name
Dim i As Long, n As Long

With ActiveSheet
For i = 1 To 10 Step 2
n = n + 1
.Names.Add "name" & n, Cells(i, 1)
Next
' also add a named formula
.Names.Add "name" & n, "=" & Range("a1").Address & "*2"
End With

Set col = New Collection
For Each nm In ActiveSheet.Names
col.Add nm, nm.Name
Next

For i = 1 To col.Count
Debug.Print TypeName(col(i)), col(i).RefersTo, col(i).Name
Set nm = col(i) 'would fail if not a name
Next

End Sub

Regards,
Peter T

 

[Tim Richardson]

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

thanks. That worked. But why does this fail:
"myCollection.Add (tmpName) "


and why does this work?
myCollection.Add tmpName

 

[Dave Peterson]

The parenthesis makes excel want to "evaluate" what's inside them. And that
means that tmpname gets passed as just the name (tmpname.value which would
evaluate to something like: =sheet1!$a$1).

So in one case (with the parens) you're passing a bunch of strings to the
collection. In the other (without the parens), you're passing real name
objects--and all their properties.

Kind of close to the difference between

Dim myVal As Variant 'not range!
myval = activesheet.range("a1:a10")
'or
set myval = activesheet.range("a1:a10")

The top creates a 10 row by 1 column array of values.
The bottom assigns a range object to A1:A10. And all those properties are still
available.

(kind of...)

 

[Dave Peterson]

"as just the name"
would be better as:
"as just the VALUE"
(not name)