Use a custom class in a For Each loop.

[JSA715]

I would like to iterate through items in a custom class using for each. Kinda like implementing the IEnumerable in .NET.

Any ideas? Is this possible in VBA?

 

[ChrisM]

Can you create a custom collection and add them to that when you create the class objects, then iterate through the collection?

 

[JSA715]

Yeah I guess I could do that... That would probably be more helpful.

Thanks for the idea.

 

[Colin Legg]

Can you explain more what you mean by "items in a custom class"? Do you mean that you have created your own custom collection class (perhaps so you can control the type of object added into the collection) and you wish to loop through its members using a For... Each loop? (If that is the case then yes, you can do that in VBA, with a little fiddling around).

 

[JSA715]

Yep, thats exactly what I mean. Can you point me in the right direction to do that?

 

[Colin Legg]

Certainly, I'll give you a basic template to develop on.

First, a standard code module called basTesting:

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Sub[/COLOR] test()
 
    [COLOR=blue]Dim[/COLOR] cCollection [COLOR=blue]As[/COLOR] clsCollection
    [COLOR=blue]Dim[/COLOR] wst [COLOR=blue]As[/COLOR] Worksheet
    [COLOR=blue]Dim[/COLOR] obj [COLOR=blue]As[/COLOR] [COLOR=blue]Object[/COLOR]
 
    [COLOR=blue]Set[/COLOR] cCollection = [COLOR=blue]New[/COLOR] clsCollection
 
    [COLOR=green]'for this example, let's add all the worksheets in this workbook into[/COLOR]
    [COLOR=green]'our custom collection[/COLOR]
    [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] wst [COLOR=blue]In[/COLOR] ThisWorkbook.Worksheets
        cCollection.Add wst
    [COLOR=blue]Next[/COLOR] wst
 
    [COLOR=green]'now let's prove we can use a[/COLOR]
    [COLOR=green]'For each loop to loop through our custom collection[/COLOR]
    [COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] obj [COLOR=blue]In[/COLOR] cCollection
 
        [COLOR=blue]Stop[/COLOR]
 
    [COLOR=blue]Next[/COLOR] obj
 
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Then a custom class called clsCollection

[COLOR=blue]Option[/COLOR] [COLOR=blue]Explicit[/COLOR]
 
[COLOR=blue]Private[/COLOR] fWorksheets [COLOR=blue]As[/COLOR] Collection
 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Class_Initialize()
    [COLOR=blue]Set[/COLOR] fWorksheets = [COLOR=blue]New[/COLOR] Collection
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=green]''''''''''''''''''''''''''''''''''''''''''''''[/COLOR]
[COLOR=green]'interface[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] Add([COLOR=blue]ByVal[/COLOR] wst [COLOR=blue]As[/COLOR] Excel.Worksheet)
    fWorksheets.Add wst
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
 
[COLOR=green]'you will want other methods/properties such as Count / Remove / Item[/COLOR]
[COLOR=#008000]'so it feels like a VBA collection[/COLOR]
[COLOR=#008000][/COLOR] 
 
[COLOR=green]'enable For...Each loops[/COLOR]
[COLOR=blue]Public[/COLOR] [COLOR=blue]Function[/COLOR] NewEnum() [COLOR=blue]As[/COLOR] IUnknown
    [COLOR=blue]Set[/COLOR] NewEnum = fWorksheets.[_NewEnum]
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

The trick to this is once you have your custom class module, you need to export it and open it up with notepad. Then you need to add in an additional attribute statement:

Public Function NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemID = -4
    Set NewEnum = fWorksheets.[_NewEnum]
End Function

And then import the class back into your project. You will not be able to see it in the VBA Editor; it is hidden.

Hope that gets you started...

 

[JSA715]

Man thats brilliant. I'll work on it tomorrow and let you know the outcome... Thanks Colin!

 

[paipimenta]

Praise be to Colin Legg! I'm making classes to implement an audit form at work more maintainably, and this saved the day!!!

3 cheers...

For i = 1 To 3
    MsgBox "Hip hip.... hooray!"
Next i

 

[JSA715]

Just thought I would update this post.

I ended up not implementing the provided interface because it can only be used on Collection objects and eventually refactored to not need it.

Well yesterday I found myself really wanting to implement a For Each type loop on one of my custom classes, so I did the following (naming is a work in progress):

Public Function ForEachItem(ByRef item As RecapItem) As Boolean
  Dim result As Boolean
 
  result = False
  Set item = Me.GetRecapItem(currentPos)
 
  If Not item Is Nothing Then
    currentPos = currentPos + 1
    result = True
  Else
    Me.ResetPos
  End If
 
  ForEachItem = result
End Function

And you can use it like so:

 While manager.ForEachItem(recapItem)
    Debug.Print recapItem.BranchInfo.Name
  Wend

Thoughts? Anyone have any other implementations? The only problem with this is if you stop the loop before it finishes, you'll need to call ResetPos() prior to looping again.

I was also thinking about the MoveNext(), Current(), MoveFirst() implementation.

 

[mcbishop]

Thank you Colin Legg! Your for-each hack helps me a lot.