VBA loop through objects via FOR EACH

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.

Creating a function to initiate and return your class will greatly reduce and simplify your code. You might even consider creating another function to return the collection.

Sub Main()
    Dim Item As Object

    For Each Item In getCategoryCollection
        MsgBox col.Name
    Next col.Item
End Sub

Function getCategoryCollection() As Collection
    Dim col As New Collection

    col.Add getNewCat("Kat1", 14, 17)
    col.Add getNewCat("Kat1", 18, 21)
    col.Add getNewCat("Kat1", 22, 25)
    col.Add getNewCat("Kat1", 26, 29)
    col.Add getNewCat("Kat1", 34, 37)
    col.Add getNewCat("Kat1", 38, 41)
    col.Add getNewCat("Kat1", 42, 45)
    col.Add getNewCat("Kat1", 46, 49)
    col.Add getNewCat("Kat1", 50, 54)

End Function

Function getNewCat(CatName As String, CatParam1 As Long, CatParam2 As Long) As Category
    Dim Kat1 As New Category
    Kat1.init CatName, CatParam1, CatParam2
    Set getNewCat = getNewCat
End Function

Alternatively if you don't want to use variants you can label the iterator as the class module.

Ex: Dim temp As category 'Used in the for each loop

Note: I think you would prefer to use an ArrayList object to store your objects.

Here is what the code would look like with the ArrayList and temp as the object iterator

Sub test()

    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 myList As Object 'Used for the arrayList
    Dim temp As category 'Used in the for each loop
    Set myList = CreateObject("System.Collections.ArrayList")

    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

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


    For Each temp In myList
       MsgBox temp.<name of property>
    Next

End Sub