[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

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