Looping Through a List of Items

Here's a quick and dirty way to loop through a list of items in VBA.

Looping Through a List of Items

Sometimes you just need a quick and dirty way to loop through a predefined list of items.  For these situations, I like to combine the Array() function with a For Each loop.  

Sample Code

Sub TestForEachArray()

    Dim DayName As Variant
    For Each DayName In Array("Mon", "Tue", "Wed", "Thu", "Fri")
        Debug.Print DayName
    Next DayName
End Sub
Results of running the sample code

Some notes

To make this work, the iterator variable must be declared as a Variant.  You lose the benefits of type safety with Variants, which is why I labeled this a "quick and dirty" solution.

The order the items appear in the call to the Array() function is the same order they will be accessed within the loop.

You can stuff just about anything into a Variant, including custom object instances.  Also, the Array() function will gladly accept a mix of Variants with different underlying data types all in the same function call.  So, while I can't think of any reason you would want to do this, at least now you know you can do this:

Sub TestForEachOlio()
    Dim Auto As oVehicle
    Set Auto = NewVehicleObject("Ford", "Mustang", 1968)

    Dim Item As Variant
    For Each Item In Array("One", #2/2/2020#, 3, 4.56, #7:00:00 AM#, Null, Auto)
        If TypeOf Item Is oVehicle Then
            Debug.Print Item.Make, Item.Model, Item.Year
            Debug.Print Item
        End If
    Next Item
End Sub
*NOTE: The oVehicle class is defined in the article, "Strongly Typed Collections in VBA."
Just because you can...doesn't mean you should.

Image by anncapictures from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0