The Ultimate Guide To Collections in Excel VBA

“I’m not a builder of buildings, I’m a builder of collections” – Leonard Lauder

The table below will show you how to use collections in VBA. For a simple and complete guide to Collections in VBA check out the rest of this post.

A Quick Guide to VBA Collections

Introduction

Collections are a very important part of VBA.  If you have used the language for any length of time then you will have used Collections. The most common ones are the Workbooks, Worksheets, Range and Cells collections. The following code shows some examples of using the VBA Workbooks collection

' Workbooks is a collection of all open workbooks

' Count is the number of workbooks in the collection
Debug.Print Workbooks.Count

' Print the full name of the workbook called Example.xlsm
Debug.Print Workbooks("Example.xlsm").FullName

' Print the full name of the workbook that was opened second
Debug.Print Workbooks(2).FullName

Collections are similar to arrays so it is important to understand what they are and how the differ to arrays.

What is a Collection?
[image error]

© Vlaskoff | Dreamstime.com. A Collection of the old Soviet cars

 

Collections and arrays are both used to group variables. They both store a set of similar items e.g. a list of student marks or country names. Using a collection or array allows you to quickly and easily manipulate a large number of items.

In my post on arrays, I explained in simple terms what arrays are and why they are so useful. I will briefly recap this information here.

If you were storing the marks of one student then you can easily do this using a single variable

Dim mark As Long
mark = sheetMarks.Range("A1")

However most of the time you will have more than one student to deal with. Imagine you want to store the marks of 100 students. If you didn’t use collections or arrays you would need to create a hundred variables – one variable to store the mark for each student.

Another problem is that you have to use these variables individually. If you want to store 100 marks then you need a line of code each time you want to store a value to a variable.

' Declare a variable for each mark
Dim mark1 As Long
Dim mark2 As Long
.
.
.
Dim mark100 As Long

' Store the marks from the worksheet in a variable
mark1 = sheetMarks.Range("A1")
mark2 = sheetMarks.Range("A2")
.
.
.
mark100 = sheetMarks.Range("A100")

As you can see in the above example, writing code like this would mean hundreds of lines of repetitive code. When you use a collection or array you only need to declare one variable. Using a loop with a collection or arrays means you only need one line for add or reading values.

If we rewrite the above example using a collection then we only need a few lines of code

' Create collection
Dim collMarks As New Collection

' Read 100 values to collection
Dim c As Range
For Each c In Sheet1.Range("A1:A100")
' This line is used to add all the values
collMarks.Add c.Value
Next

 

Collections Vs Arrays?

We have looked at what collections and arrays have in common. So what is the difference and why use one over the other?

The main difference is that with an array you normally set the size once. This means that you know the size before you start adding elements. Let me explain this with an example.

Example: Where an Array is Better

Imagine you have a worksheet of student marks with one student per row

Student Marks

You want to store information about each student. In this example you can easily count the number of rows to get the number of students. In other words you know the number of items in advance.

' Get last row - this is the number of students
Dim lStudentCount As Long
lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

' Create array of correct size
Dim arr() As Long
ReDim arr(1 To lStudentCount)

In the example code you can see that we get the number of students by counting the rows. We can then use this to create an array of the correct size.

Let us now look at a second example where we don’t know the number of items in advance

Example Where a Collection is Better

In this example we have the same student worksheet but this time we only want the of students with a given criteria. For example only the students from the USA or England that study Maths or History. In other words you will not how to select a student until you read their details from the worksheet.

Imagine also that students can be added or removed from the list as the application runs.

So in this example the number of students is not fixed and changes a  lot. Here you do not know the number of students in advance. Therefore you do not know what size array to create.

You could create an array of the biggest possible size. The problem is you would have a lot of empty slots and would have to add code to deal with these. If you read 50 students from a max of 1000 then you would have 950 unused array slots.

You could also resize the array for each item as it is added. This is very inefficient and quite messy to do. So for this example using a collection would be better.

' Declare
Dim coll As New Collection

' Add item - VBA looks after resizing
coll.Add "Apple"
coll.Add "Pear"

' remove item - VBA looks after resizing
coll.Remove 1

When you add or remove an item to a collection VBA does all the resizing for you. You don’t have to specify the size or allocate new spaces. VBA does it under the hood. All you have to do is add an item or remove it.

Another Advantage of Collections

Collections are much easier to use than arrays especially if you are new to programming. Most of the time you do three things with collections:

Create the collection
Add some items
Read through the items

So if you are not dealing with a larger number of items then using a Collection can be much neater to use.

A Disadvantage of Collections

Collections are read-only.You can add or remove an item but you cannot change the value of the item. If you are going to be changing the values in a group of items then you will need to use an array.

Now that we know when and why to use a collection let’s look at how to use one.

How to Create a Collection

You can declare and create in one line as the following code does

' Declare and create
Dim coll As New Collection

As you can see you don’t need to specify the size. Once your collection has been created you can easily add items to it.

You can also declare and then create the collection if and when you need it.

' Declare
Dim coll As Collection

' Create Collection
Set coll = New Collection

 

Minor Difference Between These Methods

The difference between these methods is that for the first one the collection is always created. For the second method the collection is only created when the Set line is reached. So you could set the code to only create the collection if a certain condition was met

' Declare
Dim coll As Collection

' Create Collection if a file is found
If filefound = True Then
Set coll = New Collection
Endif

The advantage to using this method is minimal. Allocating memory was important back in the 1990’s when computer memory was limited. Unless you are creating a huge number of collections on a slow PC you will never notice any benefit.

Use Set means the collection will behave differently than when you set the collection to nothing. The next section explains this.

Removing All items from a Collection

To remove all items from a collection you can simply set it to nothing.

Set Coll = Nothing

An important point to understand here is that what this does depends on how you created the collection. As we saw you can create a Collection by declaring using New or by using Set and New. Let’s look at both types

Declaring Using New

If you set this collection to nothing then it will be set to the state where the “object is not set”. When you add a new item VBA automatically sets the Collection variable to a valid collection.

In other words if you set the collection to nothing it will empty all the items. If you then add an item to the collection you will now have a collection with one item. This makes it simple to empty a collection.

The following code demonstrates this.

Sub EmptyColl()

' Create collection and add items
Dim coll As New Collection

' add items here

' Empty collection
Set coll = Nothing

' Add item
coll.Add "Pear"

End Sub

A subtle point to emphasize here is that when you set the collection to Nothing it is not actually set to nothing. Therefore if you try to compare it with being it will not work.

Using Set and New

When you use Set to create a collection you must create the collection again if you set it to Nothing. In the following code after setting to nothing you must then set using new again. If you don’t do this you will get the error: “Object Variable or With block variable not set”.

Sub EmptyCollSet()

' Create collection
Dim coll As Collection
Set coll = New Collection

' Add items here

' Empty collection
Set coll = Nothing

' SET TO NEW BEFORE USING
Set coll = New Collection

' Add item
coll.Add "Pear"

End Sub

 

Remove All – An Alternative Method

The following method will also remove all the elements of a collection but is a slower way to do it. The advantage is that is will work no matter which way you create the collection.

Sub RemoveAll(ByRef coll As Collection)

Dim i As Long
For i = coll.Count To 1 Step -1
coll.Remove i
Next i

End Sub

 

Adding items to a Collection

It is simple to add items to a collection. You use the add property followed by the value you wish to add.

collFruit.Add "Apple"
collFruit.Add "Pear"

You can have any basic type in a collection such as a Double

collTotals.Add 45.67
collTotals.Add 34.67

When you add items in this manner they are added to the next available index. In the fruit example, Apple is added to position 1 and Pear to position 2.

Before and After

You can use the Before or After parameters to specify where you want to place the item in the collection. Note you cannot use both of these arguments at the same time.

collFruit.Add "Apple"
collFruit.Add "Pear"
' Add lemon before first item
collFruit.Add "Lemon" Before:=1

After this code the collection is in the order

1. Lemon

2. Apple

3. Pear

collFruit.Add "Apple"
collFruit.Add "Pear"
' Add lemon after first item
collFruit.Add "Lemon" After:=1

After this code the collection is in the order

1. Apple

2. Lemon

3. Pear

Accessing Items of a Collection

To Access the items of a collection you simply use the index. As we saw the index is the position of the item in the collection based on the order they were added. The order can also be set using the Before or After parameter.

Sub access()

Dim coll As New Collection

coll.Add "Apple"
coll.Add "Pear"

' Will print Apple
Debug.Print coll(1)

' Add orange first
coll.Add "Orange", Before:=1

' Will print Orange
Debug.Print coll(1)

' Will print Apple as it is now in position 2
Debug.Print coll(2)

End Sub

You can also use the Item Property to access an item in the collection. It is the default method of the collection so the followling lines of code are equivalent

Debug.Print coll(1)
Debug.Print coll.Item(1)

 

Items in a collection are Read Only

This is a very important point. You cannot change the value of an item in a collection. When you access an item from a collection it is read only. If you try to write to a collection item you will get an error. The following code produces an “object required” error

Sub WriteValue()

Dim coll As New Collection

coll.Add "Apple"

' This line causes an ERRROR
coll(1) = "Pear"

End Sub

 

Adding different types

You can also add different types of items to a collection.

collFruit.Add "Apple"
collFruit.Add 45
collFruit.Add #12/12/2017#

This is seldom needed. In VBA the Sheets collections contains sheets of type Worksheet and of type Chart. (To create a Chart sheet simple right click on any Chart, select Move and select the radio button for New sheet).

The following code displays the type and name of all the sheets in the current workbook. Note to access different type you need the For Each variable to be a variant or you will get an error.

Sub ListSheets()

Dim sh As Variant
For Each sh In ThisWorkbook.Sheets
' Display type and name of sheet
Debug.Print TypeName(sh), sh.Name
Next

End Sub

When you access different items the For Each variable must be a variant. If it’s not you will get an error when you access a different type than you declared. If we declared sh as a worksheet in the above example it would give an error when we try to access a sheet of type Chart.

It is rare that you would need a collection of different types but as you can see sometimes it can be useful.

Adding Items Using a Key

You can also add items using a key as the next example shows

collMark.Add Item:=45, Key:="Bill"

Debug.Print "Bill's Marks are: ",collMark("Bill")

I included the parameter names to make the above example clear. However you don’t need to do this. Just remember the key is the second parameter and must be a unique string.

The following code shows a second example of using  keys

Sub UseKey()

Dim collMark As New Collection

collMark.Add 45, "Bill"
collMark.Add 67, "Hank"
collMark.Add 12, "Laura"
collMark.Add 89, "Betty"

' Print Betty's marks
Debug.Print collMark("Betty")

' Print Bill's marks
Debug.Print collMark("Bill")

End Sub

Using keys is has three advantages:

If the order changes your code will still access the correct item
You can directly access the item without reading through the entire collection
It can make you code more readable

In the VBA Workbooks collection it is much better to access the workbook by the key(name) the by the index.  The order is dependent on when they were opened and so is quite random.

Sub UseAWorkbook()

Debug.Print Workbooks("Example.xlsm").Name

Debug.Print Workbooks(1).Name

End Sub

 

When to Use Keys

An example of when to use keys is as follows: Imagine you have a collection of IDs for a 10,000 students along with their marks.

You also have a number of worksheet reports that have lists of student IDs. For each of these worksheets you need to print the mark for each student.

You could do this by adding the 10,000 students to a collection using their student id as they key. When you read an ID from the worksheet you can directly access this student’s marks.

If you didn’t use a key you would have to search through 10,000 IDs for each ID on the report.

Shortcoming of Using Keys in Collections

There are three issues with using keys in collections

You cannot check if the key exists
You cannot change the key
You cannot retrieve the key

VBA contains a class similar to the Collection called the Dictionary. With a Dictionary you always use keys to add an item. The Dictionary provides more functionality to work with keys. If you need more functionality with keys then you may find the Dictionary very useful.

Coming back to Collections: if you will need to directly access a single item then using keys can be very useful. Otherwise you don’t need to use them.

Accessing all items in a Collection

To access all the items in a collection you can use a For loop or a For Each loop. Let’s look at these individually.

Using the For Loop

With a normal For Loop, you use the index to access each item. The following example prints the name of all the open workbooks

Sub AllWorkbook()

Dim i As Long
For i = 1 To Workbooks.Count
Debug.Print Workbooks(i).Name
Next i

End Sub

You can see that we use the range of 1 to Workbooks.Count. The first item is always in postion one and the last item is always in the position specified by the Count property of the collection.

The next example prints out all the items in a user created collection.

Sub UserCollection()

' Declare and Create collection
Dim collFruit As New Collection

' Add items
collFruit.Add "Apple"
collFruit.Add "Pear"
collFruit.Add "Plum"

' Print all items
Dim i As Long
For i = 1 To collFruit.Count
Debug.Print collFruit(i)
Next i

End Sub

 

Using the For Each

The For Each loop that is a specialised loop the is used for Collections. It doesn’t use the index and the format is shown in the following example

Sub AllWorkbookForEach()

Dim book As Variant
For Each book In Workbooks
Debug.Print book.Name
Next

End Sub

The format of the For loop is:

For i = 1 To Coll.Count

Next

where i is a long and Coll is a collection.

The format of the For Each Loop is:

For Each var In Coll

Next

where var is a variant and Coll is a collection.

To access each the item

For: Coll(i)

For Each: Var

The following example shows the loops side by side for the above user collection example

Sub UseBothLoops()

' Declare and Create collection
Dim collFruit As New Collection

' Add items
collFruit.Add "Apple"
collFruit.Add "Pear"
collFruit.Add "Plum"

' Print all items using For
Dim i As Long
For i = 1 To collFruit.Count
Debug.Print collFruit(i)
Next i

' Print all items using For Each
Dim fruit As Variant
For Each fruit In collFruit
Debug.Print fruit
Next fruit

End Sub

 

For Each Versus For

It is important to understand the difference between the two loops.

The For Each Loop

is faster
is neater to write
has one order  only – low index to high

The For Loop

is slower
is less neater to write
can access in different order

Let’s  compare the loops under each of these attributes

Speed

The For Each is considered faster than the For Loop. Nowadays this is only an issue if you have a large collection and/or a slow PC/Network.

Neater

The For Each loop is neater to write especially if you are using nested loops. Compare the following loops. Both print the names of all the worksheets in open workbooks.

Sub PrintNamesFor()

' Print worksheets names from all open workbooks
Dim i As Long, j As Long
For i = 1 To Workbooks.Count
For j = 1 To Workbooks(i).Worksheets.Count
Debug.Print Workbooks(i).Name, Workbooks(i).Worksheets(j).Name
Next j
Next i

End Sub

Sub PrintNamesForEach()

' Print worksheets names from all open workbooks
Dim bk As Workbook, sh As Worksheet
For Each bk In Workbooks
For Each sh In bk.Worksheets
Debug.Print bk.Name, sh.Name
Next sh
Next bk

End Sub

The For Each loop is much neater to write and less likely to have errors.

Order

The order of the For Each loop is always from the lowest index to the highest. If you want to get a different order then you need to use the For Loop. The order of the For Loop can be changed. You can read the items in reverse. You can read a section of the items or you can read every second item.

Sub ReadRightToLeft()

' Go through sheets from right to left
Dim i As Long
For i = ThisWorkbook.Worksheets.Count To 1 Step -1
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i

' Go through first 3 sheets
For i = 1 To 3
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i

' Go through every second sheet
For i = 1 To ThisWorkbook.Worksheets.Count Step 2
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i

End Sub

The For loop gives more flexibility here but the reality is that most of the time the basic order is all you need.

Using Collections with Functions and Subs

Using a Collection as a parameter or return value is very easy to do. We will look at them in turn.

Passing a Collection to a Sub/Function

It is simple to pass a collection to a function or sub. It is passed like any parameter as the following code example shows

Sub UseColl()

' Create collection
Dim coll As New Collection

' Add items
coll.Add "Apple"
coll.Add "Orange"

' Pass to sub
PrintColl coll

End Sub

' Sub takes collection as argument
Sub PrintColl(ByRef coll As Collection)

Dim item As Variant
For Each item In coll
Debug.Print coll
Next

End Sub

You can see how useful the sub PrintColl is in the example. It will print all the elements of ANY collection. The size or type of element does not matter. This shows how flexible collections are to use.

Passing ByVal versus ByRef

One subtle point to keep in mind here is passing by value(By Val) and passing by reference(ByRef) differ slightly.

For a simple variable passing by value means a copy is created. This means if the Function/Sub changes the value will not be changed when you return to the calling procedure.

In the following example we pass total using both ByVal and ByRef. You can see that after we pass using ByRef the value has changed in the calling procedure.

Sub PassType()

Dim total As Long
total = 100

PassByVal total
' Prints 100
Debug.Print total

PassByRef total
' Prints 555
Debug.Print total

End Sub

Sub PassByVal(ByVal total As Long)
' value changed only in this sub
total = 555
End Sub

Sub PassByRef(ByRef total As Long)
' value also changed outside this sub
total = 555
End Sub

Using ByVal and ByRef with a Collection is a bit different. If you add or remove item then the collection in the original caller will also be changed. So the Subs in the following example will both remove the first item of the original collection

Sub RemoveByRef(ByRef coll As Collection)
coll.Remove 1
End Sub

Sub RemoveByVal(ByVal coll As Collection)
coll.Remove 1
End Sub

The reason for this is that a Collection variable contains a pointer. This means it contains the address of the collection rather than the actual collection. So when you add or remove an item you are changing what the pointer is pointing at and not the pointer itself. However if you change the pointer it will be changed outside of the sub.

You don’t need to worry about pointers. All you need to know is how this affects the behaviour of passing a parameter. If you set a collection parameter to nothing then the behaviour depends on if you used ByRef or ByVal.

Using ByRef will reset the original collection
Using ByVal will not change the original collection


' Will empty original collection
Sub PassByRef(ByRef coll As Collection)
Set coll = Nothing
End Sub

' Will NOT empty original collection
Sub PassByVal(ByVal coll As Collection)
Set coll = Nothing
End Sub

 

Returning a Collection From a Function

Returning a collection from a Function is the same as returning any object. You need to use the Set keyword. In the following example you can see how to return a collection

Sub FruitReport()

Dim coll As Collection
' assign coll to return value
Set coll = CreateCollection

End Sub

Function CreateCollection() As Collection

Dim coll As New Collection

coll.Add "Plum"
coll.Add "Pear"

' Return collection
Set ReadFruit = coll

End Function

Note that you don’t use the New keyword when declaring the collection in the sub FruitReport(). This is because the collection is created in CreateCollection(). When you return the collection you are simple assigning the collection variable to point to this collection.

Conclusion

Collections are a very useful part of VBA. There are much easier to use than Arrays and are very useful when the number of items changes a lot. They have only four properties: Add, Remove, Count and Item. This makes them very easy to master.

I hope you found this post useful. This blog has loads more posts on the important areas of VBA. You can check out all the posts by category here. If you have any queries feel free to email me at paulkellykk@gmail.com.