The VBA Collection Object

Use VBA collections to group key-item pairs of related data. The VBA Collection object is a default class in Excel so it doesn’t require a reference to an object library.

In case you haven’t already done so, also check out our VBA Dictionary tutorial. Dictionaries are somewhat related to VBA collections and people sometimes use them interchangeably.

You may already be more familiar with collections in VBA than you think since several of Excel’s built-in methods return collections for you to work with. Examples include the Workbooks, Worksheets, Columns and Cells collections. Common to all VBA collections, including the collections you create yourself using the collection object, is that you can reference an item either by index or by name (key). We’ll return to this later.

First, we’ll start by showing you the basic setup of the VBA collection object along with a simple way to add key-item pairs to it and retrieve items from it. After that, we’ll present the methods and properties of the collection object, which should give you a good idea of how to work with your collections. Finally, we’ll discuss some useful application ideas and the pros and cons of using VBA collections compared to arrays and dictionaries.

• VBA Collections Basic setup
• Index-item vs. key-item
• VBA Collections Methods and Properties
• Pros and Cons of VBA Collections

Now, let’s get started with the basic setup and an easy-to-follow example of writing to and reading from a collection!

VBA Collections Basic setup

First, create an empty sheet and paste the following data into cell “A1”:

A
B
C

Next, paste the code below into a standard code module:

Sub CreateCellsCollection()
    ' (1) set up the collection object
    ' (2) add cell values as keys and the cell address as the value (item)
    ' (3) print the items of the collection to the immediate window
    Dim colCells As Collection
    Dim rngCell As Range
    

    Dim cItem As Variant
    
    ' (1) set up the collection object
    Set colCells = New Collection
    
    ' (2) add cell values as keys and the cell address as the value (item)
    For Each rngCell In Range("A1:A3")
        colCells.Add Item:=rngCell.Value, Key:=rngCell.Address
    Next rngCell
    
    ' (3) print the items of the collection to the immediate window
    For Each cItem In colCells
        ' *******************************
        ' insert your code here
        ' *******************************
        Debug.Print cItem ' remove in production
    Next cItem

End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

The CreateCellsCollection procedure above provides an alternative to VBA arrays and VBA dictionaries for storing the values of a range. Let’s break down in more detail how it works.

First, we set the colCells variable to hold the collection object we’re working with. Since we’re using early binding with the New operator, this allows us to view the methods at design time with the Auto List Members feature of the Visual Basic Editor:

VBA Collection Object Auto List Members

Next, we add keys and items to our collection. For this purpose, we pass Range("A1:B3"), but you can use any other range object you want as long as it contains key-item pairs. Note, you don’t really have to add keys to the collection since it automatically creates a unique index for each item. We’ll return to this in the next section.

Finally, we loop through the items of the collection and print them to the immediate window. Notice that the cItem variable is the Variant type, since collections accepts all data types. This way we ensure that the last loop of the CreateCellsCollection procedure will still work, even if you rewrite the first loop to add data types other than cell values to the collection.

Index-item vs. key-item

In the previous example we added keys to the collection object with the cell addresses of a range to ensure each was unique. However, adding keys is not strictly necessary as the collection object automatically adds an index to the item even if you don’t specify your own key.

In other words, if you substitute

colCells.Add Item:=rngCell.Value, Key:=rngCell.Address

with

colCells.Add Item:=rngCell.Value

and run the CreateCellsCollection procedure. It will print the exact the same values (collection items) to the immediate Window. In the original procedure, you can reference the first item by referring to its key the following ways:

Debug.Print colCells.Item("$A$1")

or

Debug.Print colCells.Item(1)

If you didn’t specify your own key, only the latter method way will work.

That’s a basic overview of the VBA Collections object. Now, let’s go a bit deeper into the methods of the collection object and show you some shorthand codes to make your life a little easier when you’re working with them.

VBA Collections Methods and properties

The collection object contains the following four methods:

All the methods in the table above should be used in conjunction with the variable holding your collection object, i.e. the colCells variable in the examples above. Therefore, we’ve added dots in front of the methods in the table. The names of all the methods intuitively explain what each one does. The most striking feature of the table is that the .Item method is completely redundant, since it can be replaced with shorthand code!

Apart from the methods in the table above, the following properties of VBA collections should be pointed out:

• Collections auto-resize. You can practically add as many key-item pairs to a collection as you want and, unlike VBA arrays, the collection will automatically dimension itself.

• Properties of keys
  • Keys are unique! If you try to add a key which already exists in the collection, you will get the 457 error message at run-time.

  
  VBA Run-time Error 457

  • Keys are write-only! Once you’ve added a key, there is no easy way to retrieve it from the collection. You can’t easily run a query to grab a list of all your key names, like you can with dictionaries.
  • By default, the VBA collection keys are not case-sensitive.
  • The most commonly used data types used for collection keys are strings and numbers. Other data types are allowed but not recommended due to the write-only property of keys. Regardless of whether or not you specify a key for an item, the VBA collection object will autogenerate an index number which corresponds to that item.
  • When an item of a collection is removed, the remaining index numbers will automatically be adjusted accordingly. For instance, if you have a collection of 10 items and remove the item with index 5, index 10 will then become index 9 and index 9 will become index 8 and so on until index 4. Recall that collections generate indexes regardless of whether you’ve specified keys for your items or not!
    
    

• Properties of items.

  • Items of a collection do not have to share the same data type.
  • Once you’ve stored an item in a collection it cannot be changed, you can only read it or delete the entire index-item or key-item pair. If you attempt to change a collection item in a procedure without error handling, Excel will generate the error message 424 at run-time.

VBA Run-time Error 424

Pros and Cons of VBA Collections

Let’s summarize some of the most important pros and cons of using collections. Here, we’ll compare VBA collections to some of their main competition: arrays and dictionaries:

Collection Pros

• Keys. Using keys rather than indexes in collections makes it easier and more intuitive to reference the items than in indexed arrays.
• Auto-resizing. Unlike VBA arrays, collections auto-resize when a new item is added to it or removed from it. This makes collections particularly useful when the number of elements you’ll be grouping is unknown.
• No object library reference. Unlike with dictionaries, collections do not require a reference to an object library.
• Easy to add and remove items. Compared to arrays, it is much easier to add and remove items from collections with the built-in Add and Remove methods of the collection object.

Collection Cons

• Read-only items. This property makes collections stand apart from both arrays and dictionaries and should be taken into consideration before you choose which type of grouping method you want to use. Otherwise, you risk having to refactor your entire code once you realize you need to make changes to items in your collections!
• Copying and reinserting data from a range. Arrays are much more suitable if you need to process data from a multi-column range and then reinsert it into the same range. This is because arrays can have a multi-dimensional structure, whereas collections are limited by their index-item/key-item structure. In short, you cannot set the value of a range equal to the contents of a collection, but you can with a multi-dimensional array.
• Write-only keys. The VBA collection object does not come with a “Keys” method like the dictionary object, which makes it difficult to retrieve the keys of the collection. If you must use a collection rather than a dictionary and you do need to list the keys of the collection, you can implement a workaround where each item in the collection contains its own key. The types of collection items that allow you to do this include:
  • Arrays
  • Collections
  • Objects of class modules
    
    This partially defeats the purpose of using collections in the first place, so only use such a workaround if you absolutely must!
• No “KeyExists” method. In continuation of the previous point, the VBA collection object does not come with a method to help you you determine whether a key already exists in the collection. This is a really bad object design and one of the main reasons we generally prefer to work with dictionaries rather than collections.

So, what’s the final verdict on collections, you ask? I’m not a fan of them, but if none of the limitations of VBA collections listed above are relevant to your task, then using collections could be an excellent alternative to arrays or dictionaries for you. If, on the other hand, one or more of the limitations do apply, you probably should stay away from them and use arrays or dictionaries instead! I don’t like using collections, but some folks do. To each his own!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.