VBA loop through objects via FOR EACH

Question

I have created a class named category, which has 3 attributes. In my main class I am setting up 10 instances of the type category. With an init-function I initialize these objects, so that I can use them. Now I want to loop through these objects. I have searched for 2 hours, and the only thing I found is that the only solution is to create a collection of these objects. You can see my code sample below:

Dim Kat1 As New category
Dim Kat2 As New category
Dim Kat3 As New category
Dim Kat4 As New category
Dim Kat5 As New category
Dim Kat6 As New category
Dim Kat7 As New category
Dim Kat8 As New category
Dim Kat9 As New category
Dim Kat10 As New category

Dim col As New Collection

Kat1.init "Kat1", 14, 17
Kat2.init "Kat2", 18, 21
Kat3.init "Kat3", 22, 25
Kat4.init "Kat4", 26, 29
Kat5.init "Kat5", 30, 33
Kat6.init "Kat6", 34, 37
Kat7.init "Kat7", 38, 41
Kat8.init "Kat8", 42, 45
Kat9.init "Kat9", 46, 49
Kat10.init "Kat10", 50, 54

col.Add Kat1
col.Add Kat2
col.Add Kat3
col.Add Kat4
col.Add Kat5
col.Add Kat6
col.Add Kat7
col.Add Kat8
col.Add Kat9
col.Add Kat10


For Each col.Item In col
   MsgBox (<name of the object>)
Next col.Item

The last part doesn't seem to work, he stops at col.item with the error: "Argument is not optional".

What have I done wrong?

Answer 1

Use a variant for iterating through collections unless it's a defined data type (in which case, a variant will still work)

Dim x As Variant

For Each x In col
    MsgBox CStr(x)
Next

The problem in your code is that col.Item isn't a variable - it's a method to access an item from a collection and so it requires an argument. You can only use a variable to iterate in a For Each loop

Answer 2

Four points:

1: Collection items have a key and a value:

When you add a item, give it a label - the key - that can be used for later retrieval:

col.Add Kat10, "Kat10"

Your collection will have ordinals if you don't supply keys, so you can retrieve items by number; and you can can always iterate the entire collection, as Macro Man has shown in his answer.

2: Think about the interface for accessing the collection:

I'm assuming that the code you've shown is in some kind of 'PopulateCol' function that you run once: make that a privately-declared object, and expose your objects as a public collection property:

Option Explicit
Option Private Module

Private colKats as VBA.Collection 

Public Property Get Kat(Ident As Variant)  as Category 
' Return an instance of type 'Category' 
' Ident can be numeric (returns by ordinal) 
' Ident can be a string (returns by key) 
' Caller must deal with errors raised by requests for objects not in the collection

If colKats Is Nothing Then 
    PopulateCollection 
End If 

Set Kat = colKats(Ident) 

End Property 

Public Property Get KatHaus() AS VBA.Collection 
'Returns a reference to the collection of Categories 

If colKats Is Nothing 
    Then PopulateCollection
End If

Set KatHaus = colKats

End Property

Private Sub PopulateCol
' Your code here - 
End Sub

VBA secret: declaring a property in an ordinary module, instead of in a class module, makes it an application-level property - always visible, without the need to instantiate a parent object.

3: Memory Management

I don't quite trust VBA to clear up all the memory for object variables, when there's a collection, dictionary, or array holding references to the objects.

So I would strongly recommend that you call this kind of code on exit:

Private Sub ClearKats()
' Remove all the category objects from the collection
Dim i As Long


If colKats Is Nothing Then
    Exit Sub
End If

For i = colKats.Count - 1 To 0 Step -1
    Set colKats(i) = Nothing ' invokes the class destructor within 'Category'
    colKats.Remove(i)
Next i

Set colKats = Nothing

Setting an object in a collection to Nothing needs a bit of thought: the .Item() method of a collection may be returning a copy of the object instead of a reference - and you need to test that!

...And that's the real reason for having a private collection with a public interface that always retrieves by key or by ordinal: you know what the caller is getting.

In this case, you're getting references to the 'category' object, and each caller gets a reference to the object that's editable and up-to-date with all current changes. Maybe you wanted to have a strict read-only interface, with a privileged process inside your application maintaining the category data, and all the callers getting read-only copies: if so, remove the 'KatHaus' collection property and reimplement the Kat() 'Get' method to return a copy of the underlying category from colKats.

Consider using the Dictionary object instead of a VBA collection:

The Scripting.Dictionary object is faster, it has a useful 'Exists()' method to check if a named item is in the dictionary, and it exposes the Keys and Items as iterable arrays. However, you do need to be careful about the Item() and Items() methods returning ByVal copies of the underlying objects in the dictionary - not good for memory management, and not what you wanted if you needed 'live' and editable category classes.

There's also the nasty surprise of requesting an item by key, when the key and its item aren't already in the dictionary:

Set myKat = dicCol("Rover The Dog")

...Congratulations, your code ran without an error, and there's a dog in the KatHaus: you've created a new item with the value Empty, and that'll bite you when your downstream code expects an object of type 'Category'.

...So Dictionary objects aren't trouble-free: but they are fast, and more versatile than the VBA collection.