Excel VBA Collections

This tutorial will demonstrate how to use collections in VBA.

What is a Collection in VBA?

A collection is an object that holds a number of similar items that can easily be accessed and manipulated, even if there are a large number of items within the collection.

You can create your own collections, but VBA also comes with built in collections such as the Sheets Collection, which stores every sheet in the workbook. By using a For Each loop, you can iterate through each worksheet in the Sheets Collection.

Sub TestWorksheets()
Dim Sh As Worksheet
For Each Sh In Sheets
    MsgBox Sh.Name
    MsgBox Sh.Visible
Next Sh
End Sub

You can also address a specific worksheet in the collection using the index value (it’s position in the collection), or the actual name of the worksheet:

MsgBox Sheets(1).Name
MsgBox Sheets("Sheet1").Name

As worksheets are added or deleted so the Sheets collection grows or shrinks in size.

Note that with VBA collections the index number begins with 1 not with 0.

 

Collections Versus Arrays

Arrays and Collections can both store data, however they have several notable differences:

1. Arrays are multidimensional whereas collections are only single dimension. You can dimension an array with several dimensions e.g.

Dim MyArray(10, 2) As String

This creates an array of 10 rows with 2 columns, almost like a worksheet. A collection is effectively a single column.

2. When you populate your array, you need a separate line of code to put a value into each element of the array. If you had a two-dimensional array you would actually need 2 lines of code – one line to address the first column and one line to address the second column. With the Collection object, you simply use the Add method so that the new item is just added into the collection and the index value is automatically adjusted to suit.
3. If you need to delete an item of data then it is more complicated in the array. You can set the values of an element to a blank value, but the element itself still exists within the array.  If you are using a For Next loop to iterate through the array, the loop will return a blank value, which will need coding to make sure that the blank value is ignored.  In a collection you use the Add or Remove methods, and all the indexing and re-sizing is automatically taken care of. The item that has been removed disappears completely. Arrays are useful for a fixed size of data, but collections are better for where the quantity of data is liable to change.
4. Collections are Read Only whereas array values can be changed using VBA. With a collection, you would have to remove the value to be changed first and then add in the new changed value.
5. In an array, you can only use a single data type for the elements which is set when you dimension the array. However, in the array you can use custom data types that you have designed yourself. You could have a very complicated array structure using a custom data type which in turn has several custom data types below it.  In a collection, you can add use data types of data for each item.  You could have a numeric value, a date, or a string – the collection object will take any data type.  If you tried to put a string value in an array that was dimensioned as numeric, it would produce an error message.
6. Collections are generally easier to use than arrays. In coding terms, when you create a collection object, it only has two methods (Add and Remove) and two properties (Count and Item), so the object is by no means complicated to program.
7. Collections can use keys to locate data. Arrays do not have this function and require looping code to iterate through the array to find specific values.
8. The size of an array needs to be defined when it is first created. You need to have an idea of how much data it is going to store. If you need to increase the size of the array you can use ‘ReDim’ to re-size it, but you need to use the keyword ‘Preserve’ if you do not want to lose the data already held in the array. A collection size does not need to be defined. It just grows and shrinks automatically as items are added or removed.

Scope of a Collection Object

In terms of scope, the collection object is only available whilst the workbook is open. It does not get saved when the workbook is saved. If the workbook is re-opened then the collection needs to be re-created using VBA code.

If you want your collection to be available to all the code in your code module, then you need to declare the collection object in the Declare section at the top of the module window.

 

This will ensure that all your code within that module can access the collection.  If you want any module within your workbook to access the collection, then define it as a global object.

Global MyCollection As New Collection

Create a Collection, Add Items, and Access Items

A simple collection object can be created in VBA using the following code:

Sub CreateCollection()
'Create Collection
Dim MyCollection As New Collection

'Add Items to Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"
End Sub

The code creates a new object called ‘MyCollection’ and then the following lines of code use the Add method to add in 3 new values.

Loop Through Collection

You can then use code to iterate through each item in your collection:

For Each Item In MyCollection
    MsgBox Item
Next Item

Or this code will get the size of the collection with .Count and loop through each index number:

For n = 1 To MyCollection.Count
    MsgBox MyCollection(n)
Next n

The first For Each Loop is faster than the second For Next loop but it only works in one direction (low index to high). The For Next Loop has the advantage that you can use a different direction (high index to low) and you can also use the Step method to change the increment. This is useful when you want to delete several items since you will need to run the deletion from the end of the collection to the start as the index will change as the deletions take place.

Add Item to Collection

The Add method in a collection has 3 optional parameters – Key, Before, and After.

You can use the ‘Before’ and ‘After’ parameters to define the position of your new item relative to the others already in the collection.

This is done by specifying the index number that you want your new item to be relative to.

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", , 1
MyCollection.Add "Item3"

End Sub

 

In this example ‘Item2’ has been specified to be added before the first indexed item in the collection (which is ‘Item1’). When you iterate through this collection ‘Item2’ will appear first of all, followed by ‘Item1’ and ‘Item3’.

When you specify a ‘Before’ or ‘After’ parameter, the index value is automatically adjusted within the collection so that ‘Item2’ becomes index value of 1 and ‘Item1’ gets moved to an index value of 2.

You can also use the ‘Key’ parameter to add a reference value that you can use to identify the collection item. Note that a key value must be a string and must be unique within the collection.

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", "MyKey"
MyCollection.Add "Item3"

MsgBox MyCollection("MyKey")
End Sub

‘Item2’ has been given a ‘Key’ value of ‘MyKey’ so that you can refer to that item using the value of ‘MyKey’ instead of the index number (2).

Note that the ‘Key’ value has to be a string value. It cannot be any other data type. Note that the collection is Read Only, and you cannot update the key value once it has been set.  Also, you cannot check if a key value exists for a specific item in the collection or view the key value which is a bit of a drawback.

The ‘Key’ parameter has the added advantage of making your code more readable, especially if it is being handed over to a colleague to support, and you do not have to iterate through the entire collection to find that value. Imagine if you had a collection of 10,000 items how difficult it would be to reference one specific item!

Remove an Item from Collection

You can use the ‘Remove’ method to delete items from your collection.

MyCollection.Remove (2)

Unfortunately, it is not easy if the collection has a large number of items to work out the index of the item that you want to delete. This is where the ‘Key’ parameter comes in handy when the collection is being created.

MyCollection.Remove (“MyKey”)

When an item is removed from a collection the index values are automatically reset all the way through the collection. This is where the ‘Key’ parameter is so useful when you are deleting several items at once. For example, you could delete item index 105, and instantly item index 106 becomes index 105, and everything above this item has its index value moved down. If you use the Key parameter, there is no need to worry about which index value needs to be removed.

To delete all the collection items and create a new collection, you use the Dim statement again which creates an empty collection.

Dim MyCollection As New Collection

To remove the actual collection object completely, you can set the object to nothing.

Set MyCollection = Nothing

This is useful if the collection is no longer required by your code.  Setting the collection object to nothing removes all reference to it and releases the memory that it was using.  This can have important implications on speed of execution of your code, if a large object is sitting in memory that is no longer required.

Count the Number of Items in a Collection

You can easily find out the number of items in your collection by using the ‘Count’ property.

MsgBox MyCollection.Count

You would use this property if you were using a For Next Loop to iterate through the collection as it will provide you with the upper limit for the index number.

Test Collection for a Specific Value

You can iterate through a collection to search for a specific value for an item using a For Each Loop.

Sub SearchCollection()
Dim MyCollection as New Collection

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For Each Item In MyCollection
        If Item = "Item2" Then
            MsgBox Item & " Found"
        End If
Next
End Sub

The code creates a small collection, and then iterates through it looking for an item called ‘item2”. If found it displays a message box that it has found the specific item.

One of the drawbacks with this methodology is that you cannot access the index value or the key value.

If you use a For Next Loop instead, you can use the For Next counter to get the index value, although you still cannot get the ‘Key’ value.

Sub SearchCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For n = 1 To MyCollection.Count
        If MyCollection.Item(n) = "Item2" Then
            MsgBox MyCollection.Item(n) & " found at index position " & n
        End If
Next n
End Sub

The For Next counter (n) will provide the index position.

Sort Collection

There is no built-in functionality to sort a collection, but using some ‘out of the box’ thinking, code can be written to do a sort, utilizing Excel’s worksheet sorting function. This code uses a blank worksheet called ‘SortSheet’ to do the actual sorting.

Sub SortCollection()
Dim MyCollection As New Collection
Dim Counter As Long

'Build collection with random order items
MyCollection.Add "Item5"
MyCollection.Add "Item2"
MyCollection.Add "Item4"
MyCollection.Add "Item1"
MyCollection.Add "Item3"

'Capture number of items in collection for future use
Counter = MyCollection.Count

'Iterate through the collection copying each item to a consecutive cell on ‘SortSheet’ (column A)
For n = 1 To MyCollection.Count
    Sheets("SortSheet").Cells(n, 1) = MyCollection(n)

Next n
'Activate the sortsheet and use the Excel sort routine to sort the data into ascending order
Sheets("SortSheet").Activate
Range("A1:A" & MyCollection.Count).Select
    ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SortSheet"). Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("SortSheet").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Delete all the items in the collection – note that this For Next Loop runs in reverse order
For n = MyCollection.Count To 1 Step -1
     MyCollection.Remove (n)
Next n

'Copy the cell values back into the empty collection object using the stored value (Counter) for the ‘loop
For n = 1 To Counter
    MyCollection.Add Sheets("SortSheet").Cells(n, 1).Value

Next n

'Iterate through the collection to prove out the order that the items are now in
For Each Item In MyCollection
    MsgBox Item

Next Item

'Clear the worksheet (sortsheet) – if necessary, delete it as well
Sheets("SortSheet").Range(Cells(1, 1), Cells(Counter, 1)).Clear
End Sub

This code first creates a collection with the items added in a random order. It then copies them into the first column on a worksheet (SortSheet).

The code then uses the Excel sort tool to sort the data in the column into ascending order. The code could also be modified to sort into descending order.

The collection is then emptied of data using a For Next Loop. Note that the step option is used so that it clears from the end of the collection to the start.  This is because as it clears, the index values are reset, if it cleared from the start, it would not clear correctly (index 2 would become index 1)

Finally, using another For Next Loop, the item values are transferred back into the empty collection

A further For Each Loop proves out that the collection is now in good ascending order.

Unfortunately, this does not deal with any Key values that may have been entered originally, since the Key values cannot be read

Pass a Collection to a Sub / Function

A collection can be passed to a Sub or a Function in the same way as any other parameter

Function MyFunction(ByRef MyCollection as Collection)

It is important to pass the collection using ‘ByRef’. This means that the original collection is used.  If the collection is passed using ‘ByVal’ then this creates a copy of the collection which can have unfortunate repercussions.

If a copy is created using ‘ByVal’ then anything that changes the collection within the function only happens on the copy and not on the original.  For example, if within the function, a new item is added to the collection, this will not appear in the original collection, which will create a bug in your code.

Return a Collection from a Function

You can return a collection from a function in the same way as returning any object. You must use the Set keyword.

Sub ReturnFromFunction()
Dim MyCollection As Collection
Set MyCollection = PopulateCollection
MsgBox MyCollection.Count
End Sub

This code creates a sub routine which creates an object called ‘MyCollection’ and then uses the ‘Set’ keyword to effectively call the function to populate that collection. Once this is done then it displays a message box to show the count of 2 items.

Function PopulateCollection() As Collection
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"

Set PopulateCollection = MyCollection
End Function

The function PopulateCollection creates a new collection object and populates it with 2 items. It then passes this object back to the collection object created in the original sub routine.

Convert a Collection to an Array

You may wish to convert your collection into an array. You may want to store the data where it can be changed and manipulated. This code creates a small collection and then transfers it into an array

Notice that the collection index starts at 1 whereas the array index starts at 0. Whereas the collection has 3 items, the array only needs to be dimensioned to 2 because there is an element 0

Sub ConvertCollectionToArray()
Dim MyCollection As New Collection
Dim MyArray(2) As String

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For n = 1 To MyCollection.Count
    MyArray(n - 1) = MyCollection(n)
 
Next n

For n = 0 To 2
    MsgBox MyArray(n)
Next n
End Sub

Convert an Array into a Collection

You may want to convert an array into a collection. For example, you may wish to access the data in a faster and more elegant manner that using code to get an array element.

Bear in mind that this will only work for a single dimension of the array because the collection has only one dimension.

Sub ConvertArrayIntoCollection()
Dim MyCollection As New Collection
Dim MyArray(2) As String
MyArray(0) = "item1"
MyArray(1) = "Item2"
MyArray(2) = "Item3"

For n = 0 To 2
    MyCollection.Add MyArray(n)

Next n
For Each Item In MyCollection
    MsgBox Item
Next Item
End Sub

If you did wish to use a multi-dimensional array, you could concatenate the array values together for each row within the array using a delimiter character between the array dimensions, so that when reading the collection value, you could programmatically use the delimiter character to separate the values out.

You could also move the data into the collection on the basis that the first-dimension value is added (index 1), and then the next dimension value is added (index 2) and so on.

If the array had, say, 4 dimensions, every fourth value in the collection would be a new set of values.

You could also add array values to use as keys (providing that they are unique) which would add an easy way of locating specific data.