Denne side på dansk Home > Excel VBA macros > For Each Next in VBA class collections

RSS

For Each...Next loops in Excel VBA class collections

Class collections are great for structured programming, if you have many objects of the same nature.

It could be books, clients, raw materials - anything you can describe in much the same way with properties, methods and events.

I have written a page on the subject, How to make your own classes and class collections in Excel VBA, where you can download a spreadsheet with an example.

It is not that complicated, on the contrary it can make programming a lot easier, because you get a well defined structure, and you don't need a zillion variables.

There is just one small thing: You cannot just loop through your class collection with the fast loop, For Each...Next, as you can with Excel's own collections.

However there are some clever guys out there, and on the Internet I discovered a way to enable the For Each Next loop in your own class collections. More about this in a moment.

Looping with Item and a counter

Imagine a class collection, "clClients", which is a collection of clients with each client in a client class, "clClient".

Until recently I looped my own class collections with a counter and the Item method. Something like this, where "bPaid" is a Boolean property: 

With clClients
   For lCount = 1 to .Count
      If .Item(lCount).bPaid = False Then
         'Send reminder immediately!
      End If
   Next
End With

Looping with For Each...Next

It annoyed me that I couldn't use the faster and simpler For Each Next loop, but I found a solution on the Internet. The trick is to export your class module (here: "clClients"), open the file in a text editor (e.g. Notepad), add a line with some code and then import the class module again.

The point is, that the line you add in Notepad cannot be added using the VBA editor. Of course you can write it in the VBA editor, but it doesn't work.

Once you have added the line in Notepad and imported the class module, the line is invisible in VBA's editor, but it works!

Before exporting the class module I added the following Public Function to the module: 

Public Function NewEnum() As IUnknown
'This function enables loops
'with For Each...Next, because the following
'(invisible) line has been added in Notepad:
'Attribute NewEnum.VB_UserMemID = -4
    Set NewEnum = clClients.[_NewEnum]
End Function

So it is the line: 

Attribute NewEnum.VB_UserMemID = -4

you need to add in Notepad - and of course without a leading apostrophe.

Now I can loop through my class collection using For Each Next: 

Sub Example()
Dim Suspect as clClient

For Each Suspect in clClients
   If Suspect.bPaid = False Then
      'Send reminder immediately!
   End If
Next

End Sub

It may seem a trivial detail, but if you have large class collections and loop frequently, it makes a difference (speed), and it simplifies the code.

Related: