“I’m not a builder of buildings, I’m a builder of collections” – Leonard Lauder
- 1 A Quick Guide to Collections
- 2 Introduction
- 3 Collections Webinar
- 4 What is a Collection?
- 5 Collections Vs Arrays?
- 6 How to Create a Collection
- 7 Removing All items from a Collection
- 8 Adding items to a Collection
- 9 Accessing Items of a Collection
- 10 Adding different types
- 11 Adding Items Using a Key
- 12 Accessing all items in a Collection
- 13 Sorting a Collection
- 14 Using Collections with Functions and Subs
- 15 Conclusion
- 16 What’s Next?
- 17 Get the Free eBook
A Quick Guide to Collections
|Declare||Dim coll As Collection|
|Create at run time||Set coll = New Collection|
|Declare and Create||Dim coll As New Collection|
|Add item||coll.Add "Apple"|
|Access item||coll(1) or coll(2)|
|Access item added first||coll(1)|
|Access item added last||coll(coll.Count)|
|Get number of items||coll.Count|
|Access all items(For)||Dim i As Long
For i = 1 To coll.Count
|Access all items(For Each)||Dim fruit As Variant
For Each fruit In coll
|Remove all Items||Set coll = New Collection|
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.
If you are a member of the website, click on the image below to view the webinar.
(Note: Website members have access to the full webinar archive.)
What is a Collection?
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 with 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
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 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 know 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
Basic data types(i.e. variables like string, date, long, currency etc.) in a 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.
If you are storing an object in a Collection then you can change the value as the Collection stores a reference rather than the actual object.
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
The Collection does not have a RemoveAll function. However to remove all items from a collection you can simply set it to a new collection:
Set Coll = New Collection.
VBA will delete the collection because we are no longer referencing it. When we remove all items we generally want to use the collection again so we are effectively killing two birds with one stone by using this method:
' https://excelmacromastery.com/ Sub DeleteCollection() Dim coll1 As New Collection coll1.Add "apple" coll1.Add "pear" ' The original collection is deleted Set coll1 = New Collection End Sub
One subtle thing to keep in mind is that if we have two or more variables which reference the same collection then it will not be deleted(see cleaning up memory in VBA).
In the example below the original collection items are not deleted because it is still referenced by coll2
' https://excelmacromastery.com/ Sub CollectionNotDeleted() Dim coll1 As New Collection, coll2 As Collection coll1.Add "apple" coll1.Add "pear" ' Coll1 and Coll2 both reference the collection Set coll2 = coll1 ' Coll1 now references a new collection Set coll1 = New Collection ' Coll2 refers to the original collection - prints apple Debug.Print coll2(1) 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:
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:
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.
' https://excelmacromastery.com/ 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 following lines of code are equivalent:
Debug.Print coll(1) Debug.Print coll.Item(1)
Are Items in a Collection Read-Only?
This is a very important point. When a basic data type is stored in a Collection it is read-only. A basic data type is a string, date, integer, long etc.
If you try to update a Collection item you will get an error. The following code produces an “object required” error:
' https://excelmacromastery.com/ Sub WriteValue() Dim coll As New Collection coll.Add "Apple" ' This line causes an ERRROR coll(1) = "Pear" End Sub
You can change an object that is stored in a Collection:
' https://excelmacromastery.com/ Sub ChangeObject() Dim coll As New Collection Dim o As New Class1 ' Add object to collection o.fruit = "Apple" coll.Add o ' Change the fruit part of class1 coll(1).fruit = "Pear" ' Prints pear Debug.Print coll(1).fruit End Sub
This may seem like contradictory behaviour, but there is a good reason. Any item that is added to a Collection is read-only. However, when you add an object to a Collection, the object is not added as the item. A variable with the memory address of the object is added as the item.
This happens seamlessly so that you don’t notice it. The item variable is actually read-only but the object it points to is not.
All you need to remember is that basic data types in a Collection are read-only. Objects in a Collection can be changed.
You can read more about objects in memory here.
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:
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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) than by the index. The order is dependent on the order they were opened and so is quite random:
' https://excelmacromastery.com/ 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.
The Shortcoming of Using Keys in Collections
There are two shortcomings of keys in Collections
- You cannot check if the Key exists.
- You cannot update the value stored at the Key unless it is an object.
The first issue is easy to get around. The following code checks if a key exists
' https://excelmacromastery.com/ Function Exists(coll As Collection, key As String) As Boolean On Error Goto EH coll.Item key Exists = True EH: End Function
You can use it like this:
' https://excelmacromastery.com/ Sub TestExists() Dim coll As New Collection coll.Add Item:=5, key:="Apple" coll.Add Item:=8, key:="Pear" ' Prints true Debug.Print Exists(coll, "Apple") ' Prints false Debug.Print Exists(coll, "Orange") ' Prints true Debug.Print Exists(coll, "Pear") End Sub
The second issue is that it is not possible to update a value in a Collection. When can update and object and the reason for this is that the collection doesn’t actually store the object. It stores the address of the object.
If you need to update a basic value like a long, string etc. then it’s not possible. You have to remove the item and add a new one.
If you wish to use keys there is an alternative to the Collection. You can use the Dictionary. The Dictionary provides more functionality to work with keys. You can check if keys exist, update the values at keys, get a list of the keys and so on.
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
' https://excelmacromastery.com/ 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 position 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:
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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
where i is a long and Coll is a collection.
The format of the For Each Loop is:
For Each var In Coll
where var is a variant and Coll is a collection.
To access each the item
For Each: Var
The following example shows the loops side by side for the above user collection example:
' https://excelmacromastery.com/ 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
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.
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.
' https://excelmacromastery.com/ 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.
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.
' https://excelmacromastery.com/ 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.
Sorting a Collection
There is no built-in sort for the VBA collection. However we can use this QuickSort
' https://excelmacromastery.com/ Sub QuickSort(coll As Collection, first As Long, last As Long) Dim vCentreVal As Variant, vTemp As Variant Dim lTempLow As Long Dim lTempHi As Long lTempLow = first lTempHi = last vCentreVal = coll((first + last) \ 2) Do While lTempLow <= lTempHi Do While coll(lTempLow) < vCentreVal And lTempLow < last lTempLow = lTempLow + 1 Loop Do While vCentreVal < coll(lTempHi) And lTempHi > first lTempHi = lTempHi - 1 Loop If lTempLow <= lTempHi Then ' Swap values vTemp = coll(lTempLow) coll.Add coll(lTempHi), After:=lTempLow coll.Remove lTempLow coll.Add vTemp, Before:=lTempHi coll.Remove lTempHi + 1 ' Move to next positions lTempLow = lTempLow + 1 lTempHi = lTempHi - 1 End If Loop If first < lTempHi Then QuickSort coll, first, lTempHi If lTempLow < last Then QuickSort coll, lTempLow, last End Sub
You can use it like this:
' https://excelmacromastery.com/ Sub TestSort() Dim coll As New Collection coll.Add "USA" coll.Add "Spain" coll.Add "Belguim" coll.Add "Ireland" QuickSort coll, 1, coll.Count Dim v As Variant For Each v In coll Debug.Print v Next End Sub
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:
' https://excelmacromastery.com/ 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 item 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:
' https://excelmacromastery.com/ Sub PassType() Dim total As Long total = 100 PassByValue total ' Prints 100 Debug.Print total PassByReference total ' Prints 555 Debug.Print total End Sub Sub PassByValue(ByVal total As Long) ' value changed only in this sub total = 555 End Sub Sub PassByReference(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:
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ ' 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
' https://excelmacromastery.com/ Sub FruitReport() ' NOTE: We do not use New keyword here to create the collection. ' The collection is created in the CreateCollection function. Dim coll As Collection ' receive coll from the CreateCollection function Set coll = CreateCollection ' do something with coll here End Sub Function CreateCollection() As Collection Dim coll As New Collection coll.Add "Plum" coll.Add "Pear" ' Return collection Set CreateCollection = 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.
Collections are a very useful part of VBA. There are much easier to use than Arrays and are very useful when you are doing a lot of adding and removing items. They have only four properties: Add, Remove, Count and Item. This makes them very easy to master.
The main points of this post are:
- Collections are a way of storing a group of items together.
- VBA has its own collections such as Workbooks, Worksheets and Cells.
- The items do not have to be of the same type but they normally are. The VBA Sheets collection can contain both worksheets and chart sheets.
- A collection makes it easy to perform the same task on multiple items e.g. print all the values.
- Collections are similar to arrays as they both store groups of similar items.
- Collections are better when adding and removing lots of items.
- Collections are simpler to use than arrays.
- Arrays are more useful when the number of items is fixed.
- Arrays are more efficient when reading and writing to or from cells.
- Basic data types(i.e. non-objects) in a Collection are read-only whereas arrays are read/write.
- You can create a collection using Dim only or Dim with Set
- You can delete an entire collection by setting it to Nothing. What this does depends on how it was created(see last point).
- You can add items to a specific position in the collection using Before and After arguments with the collection Add function.
- You can use Keys with a collection to access an item directly. Collections do not have good support for keys so you are usually better to use the Dictionary collection when you need to use Keys.
- You can use the For and For Each loops to access all items in a collection. The For Each loop is more efficient but only allows you to go through the collection in one order.
- You can easily pass a collection as an argument to a Function or Sub.
- You can easily return a collection from a Function.
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.).
Get the Free eBook
Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.