Excel VBA: Collections

A collection of variables of the same data type is a very efficient data structure and easy to work with. Did you know that you can also define your own custom collections? Here is how!

What is a collection?

A collection is a stack of objects of the same type, “chained” one after the other.

As you add an object to the collection, it assumes the next index in sequence – added as the next “link” in the “chain”.

The collection is an easy to use data structure. It requires no declaration of the anticipated size of the “chain” in advance. You can add a member, remove a member, ask for the number of members in the collection, and the kicker: loop through all members of the collection, in order, a highly efficient and performant scan of the collection.

You cannot change the value of a member, but you can remove it and insert a new member instead.

A collection offers some additional benefits and, in a way, can be considered as a mix of a stack, a key:value store and an enumeration. We’ll see all of that soon.

Working with a collection

Some of the Excel objects are already arranged as collections.

Perhaps you are already familiar with the Worksheets collection. To refer to the first Worksheet in the collection, we can simply ask for it like that:

Sheets(1)

For example, let’s get the name of the second Worksheet in the active Workbook:

ActiveWorkbook.Sheets(2).Name

Every member of a collection may have a key associated with it, allowing us to reference it using its key rather than its index. The Worksheet’s name serves as the key to a Worksheet. This also works:

Sheets(“Planning”)

We add a new member to the collection using the Add method:

Sheets.Add

We can also specify the position, or index, of the added member in the collection using the Before or After arguments:

Sheets.Add Before:=ActiveWorkbook.Sheets(2)

How many members do we have in a collection? The Count property will inform us immediately:

Sheets.Count

Let’s remove the last Worksheet of the Workbook running our code:

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Delete

As you’ll soon see, removing a member from a custom collection you have created is done using the Remove method, not the Delete method.

The following loops through all the Worksheets in our Workbook and prints out their names:

Dim wks As Worksheet

For Each wks In Worksheets

        Debug.Print (wks.Name)

Next

Creating your own custom collection

If you are in a need to arrange objects of the same type as a collection and enjoy the benefits of a collection – you can do it, here’s how.

Let’s create a collection of regions, represented by their (string) name:

Dim CRegions As New Collection

As a collection is an object type (or a Class), the above statement creates an instance (or “copy”) of the collection class, by way of using the “New” qualifier. You probably remember this from my Objects Oriented Programming Blog posts series here.

Let’s add three members to our collection:

CRegions.Add “USA”

CRegions.Add “Europe”

CRegions.Add “Asia”

Now we can loop through our regions:

Dim varRegion as Variant

For Each varRegion in Cregions

     Debug.Print varRegion

Next

Note that I dimensioned my iteration variable, varRegion, as a Variant type, to accommodate my custom collection’s data type, whatever it is.

How many regions do I have currently in my collection?

CRegions.Count

Let’s remove Europe from the collection:

CRegions.Remove 2

If you want to clear a collection from all its members, just re-instantiate it anew:

Dim CRegions As New Collection

If you want to separate the memory allocation (dimensioning) of a new collection variable from its instantiation, you can write:

Dim CEmployees As Collection

Set CEmployees = New Collection

You may recall that the Set command must be used when setting objects’ pointers, as oppose to assigning values to variables. Anyway, you cannot use your collection variable before you instantiate it from its Class, as it does not really exist as a collection object until you do that.

Adding keys to a collection’s members

Besides referencing a collection member by its index, we can assign a key to it and reference it by its key. We already saw that with the Worksheets collection. Let’s see an example where this would make sense:

Dim CSizes As New Collection   

With CSizes

    .Add 8, "Small"

    .Add 12, "Medium"

    .Add 16, "Large"

End With

Let’s print out the Medium size:

Debug.Print CSizes("Medium")

If it reminds you of enumerations – You’re right on. Read about enumerations in Excel VBA in this Blog post from a couple of weeks ago.

The different between collection keys and enumerations, is that enumerations are labels assigned to numbers, while a collection can hold any data type, not only numbers. For example:

Dim CDept As New Collection   

With CDept

    .Add "Finance", "FIN"

    .Add "Logistics", "LGS"

    .Add "Sales", "SLS"

    .Add "Marketing", "MRK"

    .Add "Manufacturing", "MFG"

End With

Just remember that the key must be a string, not a number (you have the member’s index already maintained automatically anyway if you want a number as a reference point).

The many facets of a collection

As you can see, a collection has several characteristics that makes it a flexible data structure for many use cases.

By assigning a key to a collection’s members we are effectively defining a key:value store, a very popular data structure in many other languages and even Databases.

Also, the fundamental structure of a collection can be akin to a stack, albeit a collection has even more flexibility.

In a stack, you add a new item that “pushes” existing items as it snaps in, and calling the stack to pop out an item serves the top most (or last inserted) item in the stack. The stack I just described is called a LIFO stack (Last-In-First-Out), but a FIFO stack (First-In-First-Out) can also be easily implemented using a collection, of course.

As a custom collection can point to any data type, you can even collect your own custom data types – the subject of a coming-soon Blog post of mine. Think about a collection of employees, or orders, or vehicles…

Hey, be sure to share this Blog post with your colleagues – they’ll love ya for that!