[VBA] Introduction to Using the Collection Object! Handling Variable Lists Easily

Introduction

When handling multiple data items in VBA, arrays are very useful. However, if you need to “change the number of elements during processing,” you must use dynamic arrays and ReDim Preserve, which can be cumbersome.

The Collection object is the best solution when you want to easily add or remove elements freely. The Collection is a built-in VBA object for managing variable-length lists, allowing you to handle data intuitively, much like adding or removing items from a shopping basket.

In this article, I will explain the basic usage of Collections (creation, addition, deletion, and referencing) with concrete sample code.

Basic Usage of the Collection Object

This macro creates a Collection object, adds several items, deletes one, and writes the final list content to cells.

Completed Code

' Basic usage of the Collection object
Sub BasicCollectionExample()
    
    '--- 1. Create the Collection Object ---
    Dim itemList As Collection
    ' Note: New keyword is required to create the instance
    Set itemList = New Collection
    
    '--- 2. Add items with the .Add method ---
    itemList.Add "Laptop"
    itemList.Add "Keyboard"
    itemList.Add "Mouse"
    itemList.Add "Monitor"
    
    '--- 3. Get the number of items with the .Count property ---
    Debug.Print "Count after addition: " & itemList.Count ' -> 4
    
    '--- 4. Remove an item with the .Remove method ---
    ' Remove the 3rd item "Mouse" by index (1-based)
    itemList.Remove 3
    
    Debug.Print "Count after deletion: " & itemList.Count ' -> 3

    '--- 5. Loop through all items and write to cells ---
    Dim i As Long
    For i = 1 To itemList.Count
        ' Access each item using .Item(i) or simply (i)
        Worksheets("Sheet1").Range("B2").Offset(i - 1, 0).Value = itemList(i)
    Next i

End Sub

Explanation of the Code

1. Creating the Collection

Set itemList = New Collection

Since a Collection is an object, after declaring it with Dim, you must use the Set and New keywords to create a new instance (entity).

2. Adding Items (.Add)

itemList.Add "Laptop"

Use the .Add method to add items to the Collection. Added items are automatically assigned an index number starting from 1.

3. Getting the Item Count (.Count)

itemList.Count

The .Count property returns the total number of items currently in the Collection. It is read-only.

4. Removing Items (.Remove)

itemList.Remove 3

The .Remove method deletes the item at the specified index number (1-based). When an item is deleted, the indexes of subsequent items automatically shift forward to fill the gap.

5. Referencing Items (.Item or ())

itemList(i)

You can access each item in the Collection using .Item(index) or simply (index). The index starts at 1. The standard approach is to loop from 1 to .Count, as shown in For i = 1 To itemList.Count.

Differences from Arrays

While Collections are similar to arrays, there are significant differences:

Feature	Collection	Array
Size Resizing	Not required (Auto-expanding)	Requires ReDim Preserve
Element Type	Variant only (Stores anything)	Can be specified at declaration
Index	1-based (Fixed)	Can be 0-based or 1-based
Key Support	Possible (Add Item, Key)	Not supported

Summary

In this article, I explained how to handle flexible lists using the VBA Collection object.

• Create using New Collection.
• Add items with .Add and delete with .Remove.
• Get the number of items with .Count and access items via (Index).
• Indexes always start at 1.

In scenarios where the number of elements is unknown beforehand or where additions and deletions occur frequently during processing, using a Collection makes the code much simpler and more convenient than using arrays.