[RESOLVED] For Each...Next Loop - User Defined Class and Collection - Excel2005

[DKenny]

I am trying to use the For Each...Next looping structure with a User Defined Class and a corresponding User defined Collection Class.
From the Help files on F E...N -
"For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. "
Based on this, I assumed I would be able to use this structure with UD classes, but I can't make it happen. I keep getting the "Object doesn't support this property or method (Error 438)" error.

Here's a slimmed down version of my class, collection and a code snippet demonstrating the error.

As always - any help is appreciated.

Sample Module Code
Sub TryTests()
Dim MyTests As cTests
Dim i As Integer
Dim MyTest As cTest

Set MyTests = New cTests
For i = 1 To 5
MyTests.Add "Test" & CStr(i)
Next i

'This loop structure doesn't work
For Each MyTest In MyTests
Debug.Print MyTest.Name
Next MyTest

Set MyTest = Nothing
Set MyTests = Nothing
End Sub

"cTest" Class Code
Private pNAME As String

Property Get Name() As String
Name = pNAME
End Property

Property Let Name(nName As String)
pNAME = nName
End Property

"cTests" Collection Class Code
Private pCol As Collection

Private Sub Class_Initialize()
Set pCol = New Collection
End Sub

Private Sub Class_Terminate()
Set pCol = Nothing
End Sub

Function Add(TestName As String) As cTest
Dim NewItem As New cTest

NewItem.Name = TestName

pCol.Add Item:=NewItem, key:=TestName
Set Add = NewItem

Set NewItem = Nothing
End Function

[DKenny]

PS
I know that I can use a For..Next loop, as below, but the reason for my post is to understand why the other apporach isn't working. I'm fairly new to user defined classes and collections and want to understand if its something in my approach that is preventing the use of the For Each... approach.

Sub TryTests()
Dim MyTests As cTests
Dim i As Integer
Dim MyTest As cTest

Set MyTests = New cTests
For i = 1 To 5
MyTests.Add "Test" & CStr(i)
Next i

'This loop structure works
For i = 1 To 5
Debug.Print MyTests.Item(i).Name
Next i


Set MyTest = Nothing
Set MyTests = Nothing
End Sub

Thanks
Declan

[ZeBula8]

Is the Add method the only function in cTests?

In order to traverse the collection, you'll need another function in cTests to enumerate the data items:

Function NewEnum() As IUnknown
' allows iteration of the collection items
Set NewEnum = pCol.[_NewEnum]
End Function

[DKenny]

I've added the code as suggested, but am still getting the 438 error.
Any ideas why?
Is the Add method the only function in cTests?
No, I just removed everthing else in order to focus on the issue. In the real code there are many more properties and functions in the collection class.

[DKenny]

OK, I did a Google search on " NewEnum VBA" and found the following. (Mods: this is a post in another forum, not sure if its acceptable to post it here, if not I will remove)
NewEnum Function (http://p2p.wrox.com/topic.asp?TOPIC_ID=26259)

Basically, you need to export, then delete, the collection class and add the following line into the "NewEnum" function using a text editor.
Attribute NewEnum.VB_UserMemId = -4

After that you just re-import the class and you can use For Each...

ZeBula8 - thanks for pointing me in the right direction. :)

[si_the_geek]

(Mods: this is a post in another forum, not sure if its acceptable to post it here, if not I will remove)As it's the answer to the thread question, it's fine :)