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:
For lCount = 1 to .Count
If .Item(lCount).bPaid = False Then
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
Set NewEnum = clClients.[_NewEnum]
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:
Dim Suspect as clClient
For Each Suspect in clClients
If Suspect.bPaid = False Then
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.