VBA – Collections with Excel :)

Posted on May 19, 2014 by

Today I have learned that someone (most probably an engineer from MicroSoft) has tought of putting in VBA! Thus, making it really a fully functional programming language. Quite good!

Long story short – what are collections? Collection is a built-in class in VBA, letting you to collect things.

In this example, I will show how to create a collection of cars and to print it with a for-each loop. The code is quite self-reasonable, so I will not comment it:

Option Explicit
Sub CollectionExample()

    Dim cMyCars             As Collection
    Dim iCounter            As Integer
    Dim vCar                As Variant

    Set cMyCars = New Collection

    iCounter = 1

    cMyCars.Add "Ikarus204"
    cMyCars.Add "Man404"
    cMyCars.Add "Mercedes204"
    cMyCars.Add "Ikarus4"

    Debug.Print "We have in total " & cMyCars.Count & " cars:"

    For Each vCar In cMyCars
        Debug.Print cMyCars.Item(iCounter)
        iCounter = iCounter + 1
    Next vCar

    Set cMyCars = Nothing
End Sub

Option Explicit

Sub CollectionExample()

Dim cMyCars As Collection

Dim iCounter As Integer

Dim vCar As Variant

Set cMyCars = New Collection

iCounter = 1

cMyCars.Add "Ikarus204"

cMyCars.Add "Man404"

cMyCars.Add "Mercedes204"

cMyCars.Add "Ikarus4"

Debug.Print "We have in total " & cMyCars.Count & " cars:"

For Each vCar In cMyCars

Debug.Print cMyCars.Item(iCounter)

iCounter = iCounter + 1

Next vCar

Set cMyCars = Nothing

End Sub

Thus, once you are ready with the code you will receive the following text in the immediate (Ctrl+G) window:

Enjoy it if you can! 🙂

Tagged with: VBA collection for-each, VBA Collections, VBA Excel

VBA – Collections with Excel :)

Related posts: