Working with collections (Collection)

This lesson covers the Collection, whose number of elements you can change freely.

You create one with Dim name As New Collection and add elements with .Add. Unlike an array, you do not have to decide the size in advance — you can add and remove freely as you go.

The sample code creates a New Collection, adds fruit with .Add, and then displays every element with For Each.

A common early stumble is choosing between this and an array. "An array when the number of elements is known from the start, a Collection when it may grow or shrink while running" makes the choice easy.

In professional work, Collections are often used for data whose size is not known until run time — a list of files to process, a list of sheet names.

📖 Reference code

Sub UseCollection()
    Dim fruits As New Collection
    Dim f As Variant
    Dim result As String
    fruits.Add "apple"
    fruits.Add "orange"
    fruits.Add "grape"
    For Each f In fruits
        result = result & f & vbNewLine
    Next f
    MsgBox result
End Sub

✍️ Your code

Type your code, then press "Run"

🧪 This site can't compile or run VBA directly, so it checks on the spot whether what you typed matches the reference code (scoring happens entirely in your browser — nothing is sent anywhere).