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 spreadsheets with our free VBA Developer's Guide Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Guide to supplement this tutorial. Grab it now and we’ll also send you a bunch of pre-built macros.
First, we set the
VBA Collection Object Auto List Members
Next, we add keys and items to our collection. For this purpose, we pass
Finally, we loop through the items of the collection and print them to the immediate window. Notice that the
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
and run the
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:
|.Add||Adds an item to the collection. Besides the mandatory Item parameter, the method takes three optional parameters:
Key - adds the specified key to the item. If not specified, the collection will add the item with an index number.
After - adds a new item after the specified item index in a pre-existing collection of items.
Before - adds a new item before the specified item index in a pre-existing collection of items.
|.Count||Returns the number of items in the collection.|
|.Item||Returns the item with the specified index in the parameter. This method also works if you have used keys for the collection since the collection object automatically constructs an index for each item. Alternatively, you can specify a key in the parameter. Both examples below will work, provided the specified key is valid:
Note, however, that referencing the item can be shortened to:
|.Remove||Removes the item with the specified index in the parameter. Alternatively, you can specify a key in the parameter. Both examples below will work, provided the specified key is valid:
All the methods in the table above should be used in conjunction with the variable holding your collection object, i.e. the
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:
- 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.
- 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:
- 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.