Looping

To loop through the items in a collection you can use a For - Next loop or a For Each - Next loop.
The For Each - Next loop will iterate through the entire collection.


For - Next

You can iterate through the items of a collection using a For - Next loop
This type of loop is called an Indexed loop.
Notice that 'sMyItem' has a String data type.

Dim colMyItems As Collection 
Set colMyItems = New Collection
colMyItems.Add "Monday"
colMyItems.Add "Tuesday"
colMyItems.Add "Wednesday"

Dim lcount As Long
Dim sMyItem As String
For lcount = 1 To colMyItems.Count
   sMyItem = colMyItems.Item(lcount)
   Debug.Print sMyItem
Next lcount

For Each - Next

You can iterate through all the items of a collection using a For Each - Next loop
Using a For Each - Next loop to iterate through a collection is faster than using a For - Next loop.
The item variable must be of type Variant.
It is possible to use an Object data type but this is not recommended (see below).

alt text
Dim colMyItems As Collection 
Set colMyItems = New Collection
colMyItems.Add "Monday"
colMyItems.Add "Tuesday"
colMyItems.Add "Wednesday"

Dim vMyItem As Variant
For Each vMyItem In colMyItems
    Debug.Print vMyItem
Next vMyItem

MyItem As Variant

The item variable must be of type Variant when the collection contains a Value data type
For example: Boolean, Currency, Date, Double, Integer, Long, Single, String.

Dim vMyItem As Variant 
For Each vMyItem In colMyItems
    Debug.Print vMyItem
Next vMyItem

MyItem As Object

The item variable can be of type Object or Variant when the collection contains a Class or Object.
Try to always declare your item variable with the Variant data type when you are looping through a custom collection.

Dim colMyItems As Collection 
Set colMyItems = New Collection

Dim MyClass As class_Testing
Set MyClass = New class_Testing
colMyItems.Add MyClass
colMyItems.Add MyClass

Dim oItem As Object
For Each oItem In colMyItems
    Debug.Print TypeName(oItem)
Next oItem

To run this code, create an empty class module called "class_Testing".


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext