The Ultimate Guide To Collections in Excel VBA

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


A Quick Guide to Collections

DeclareDim coll As Collection
Create at run timeSet coll = New Collection
Declare and CreateDim coll As New Collection
Add itemcoll.Add "Apple"
Access item coll(1) or coll(2)
Access item added first coll(1)
Access item added lastcoll(coll.Count)
Get number of itemscoll.Count
Access all items(For)Dim i As Long
For i = 1 To coll.Count
   Debug.Print coll(i)
Next i
Access all items(For Each)Dim fruit As Variant
For Each fruit In coll
   Debug.Print fruit
Next fruit
Remove itemcoll.Remove(1)
Remove all ItemsSet 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.


Download the Code


Collections Webinar

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


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:


VBA Collection

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.

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:

    ' 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)


VBA Collections and Arrays

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.

Collections VBA


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:

  1. Create the collection
  2. Add some items
  3. 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.

VBA collection readonly


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


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:

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

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:
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 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:

Sub WriteValue()

    Dim coll As New Collection

    coll.Add "Apple"

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

End Sub


However you can change the class object that is stored in a Collection:

' Demonstrates that a class object can be updated in a collection.
Sub ChangeObject()

    Dim coll As New Collection
    Dim o As New Class1
    ' set value of fruit member of the class
    o.fruit = "Apple"
    ' Add object to collection
    coll.Add o
    ' Prints Apple
    Debug.Print "Object fruit is " & coll(1).fruit
    ' Change the fruit part of class1 object
    coll(1).fruit = "Pear"
    ' Prints pear
    Debug.Print "Object fruit is " & 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:

Sub ListSheets()

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

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:

  1. If the order changes your code will still access the correct item
  2. You can directly access the item without reading through the entire collection
  3. 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:

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

  1. You cannot check if the Key exists.
  2. 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

Function Exists(coll As Collection, key As String) As Boolean

    On Error Goto EH

    IsObject (coll.Item(key))
    Exists = True
End Function


You can use it like this:

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

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:

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

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: 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



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.

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:

' Read through the worksheets in different orders
' Note: You need a least 3 worksheets in the workbook or
' you will get an error.
Sub ReadRightToLeft()

    ' Go through sheets from right to left
    Debug.Print vbNewLine & "Sheets in reverse"
    Dim i As Long
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next i
    ' Read the names of the first 2 sheets
    Debug.Print vbNewLine & "First two sheets "
    For i = 1 To 2
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next i
    ' Go through every second sheet
    Debug.Print vbNewLine & "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

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
    Do While vCentreVal < coll(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    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
  If first < lTempHi Then QuickSort coll, first, lTempHi
  If lTempLow < last Then QuickSort coll, lTempLow, last
End Sub


You can use it like this:

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
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:

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

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

    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:

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()
    ' 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.


Example – Reading from a worksheet

Let’s have a look at an example of using the Collection with a worksheet.

We use this data:

VBA Collection

Student Marks

The user selects the subject in cell B2
Collection Example
In this example we read through the list of students. Any student, that is taking the subject listed in cell B2, is added to the collection.

We then write the results to the worksheet from cell F5 onwards.

You can download this example below as part of the source code for this post.

The code is here:

' The user selects a subject.
' Read through the students and add the ones with that subject
' to the collection.
' Then write out the collection to the worksheet.

Public Sub CreateReport()

    ' Get the range from the table
    Dim rg As Range
    Set rg = shStudents.ListObjects("tbStudents").DataBodyRange

    Dim coll As New Collection

    ' Read through the students
    Dim i As Long
    For i = 1 To rg.Rows.Count
        ' If the student has the selected 'subject' then add to the collection
        If rg.Cells(i, 3).Value = shStudents.Range("B2").Value Then
            coll.Add rg.Cells(i, 1).Value
        End If
    Next i
    ' clear existing data below the header
    ' Write the collection to the worksheet
    Dim item As Variant, currentRow As Long
    currentRow = 5
    For Each item In coll
        ' Write the item to the worksheet
        shStudents.Cells(currentRow, 6).Value = item
        ' Move to the next row
        currentRow = currentRow + 1
    Next item

End Sub



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:

  1. Collections are a way of storing a group of items together.
  2. VBA has its own collections such as Workbooks, Worksheets and Cells.
  3. 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.
  4. A collection makes it easy to perform the same task on multiple items e.g. print all the values.
  5. Collections are similar to arrays as they both store groups of similar items.
  6. Collections are better when adding and removing lots of items.
  7. Collections are simpler to use than arrays.
  8. Arrays are more useful when the number of items is fixed.
  9. Arrays are more efficient when reading and writing to or from cells.
  10. Basic data types(i.e. non-objects) in a Collection are read-only whereas arrays are read/write.
  11. You can create a collection using Dim only or Dim with Set
  12. You can delete an entire collection by setting it to Nothing. What this does depends on how it was created(see last point).
  13. You can add items to a specific position in the collection using Before and After arguments with the collection Add function.
  14. 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.
  15. 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.
  16. You can easily pass a collection as an argument to a Function or Sub.
  17. You can  easily return a collection from a Function.



What’s Next?

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.).


  1. Hello Paul,
    Can we create a collection like how a list is created in python?
    I mean we can create a list “Grades” in python by typing the following
    grades = [10, 20, 30]

    Can we create a collection in VBA like this instead of adding each grade one by one?

    Thank you

    1. The simple answer is no.

      You can use do it with an array

      Dim arr As Variant
      arr = Array(10, 20, 30)
  2. Hello Paul,

    This was a great post, but I’m really new at programming. I have a problem related to “Collections in VBA”

    How I add two separated words using space in Collections ?

    Example I want to add “Apple Pear” in a Collections.

    Maybe something like these code below :

    Sub TestExists()

    Dim coll As New Collection
    coll.Add Item:=5, key:=”Apple Pear”

    End Sub

    Unfortunately, the code isn’t running properly.

    I tried to add “Space(1)”, &” “& and another way between those string, but still doesn’t run properly.

    Could you please suggest another way to solve these problem ?

    Thankyou Paul.

    1. I’m not 100% clear what you mean but this code will add Apple and Pear as two different items

      Dim arr As Variant

      ‘ Split string to arr
      arr = Split(“Apple Pear”, ” “)

      coll.Add arr(0)
      coll.Add arr(1)

  3. Hello Paul

    I would like to ask about adding space betwee string in Collection.

    How I add two separated words in Key, like “Apple Pear” sample below :

    Sub TestExists()

    Dim coll As New Collection
    coll.Add Item:=5, key:=”Apple Pear”

    End Sub


    1. Hi Ali,

      First of all, the key in collections is not very useful and you may want to use the Dictionary instead.

      Secondly, the key in both structures(Collection and Dictionary) is a unique item whos purpose is to identify a particular entry. So each item can only have one key.

      I hope this helps.


      1. So the conclusion, we can’t add two separated words in one key like this one (key:=”Apple Orange”) right ?

        I just modified the “Sentiment Analysis” code in VBA. The problem is that the formula can’t caught double phrase which is important for particular situation.

        As example

        Private Sub PopulatePositive()
        With SentimentCollection
        .Add Item:=3, Key:=”like”
        End With
        End Sub

        The code to run it is ” GetSentimentValue ”

        When in the cell A1 has the word “like”, and I run the formula in cell B1 = GetSentimentValue(A1), resulting 3 in cell B1 which mean it is “Positive” sentiment.

        Unfortunately, if in the cell A1 has the word “don’t like”, the result is still the same that is 3 (Positive), instead of I need it to be (Negative) since the word “don’t” has negative connotation.

        That’s why I asked you, how to add two separated words in the same key. Is there another solution for this ?

        Thankyou for your time Paul.

  4. Hello Paul,

    I would like to ask about retrieving item from multidimensional array/object. How can I create multidimensional collection and retrieve item like col(1,2)?

    If I try debug.print cMtr.item(1)(1) or something I get object required error…

    Here my function code for create matrix and I would like to use my class object cMatrix, can you help me?

    By the way do not think below code as an obligatory, the important think is using collection with multidimensional, adding to collection array item like (2,4) node coordinate.

    Function createMatrix(width, height) As cMatrix
    Dim matrix As Variant
    Dim X, y, I, J As Integer
    Dim cMtr As New cMatrix

    ReDim matrix(width, height)

    I = 0
    Do While I < height
    J = 0
    Do While J < width
    Set matrix(I, J) = New cMatrix
    J = J + 1
    I = I + 1

    y = 0
    Do While y < height
    X = 0
    Do While X < width
    matrix(y, X) = 1
    X = X + 1
    y = y + 1

    cMtr.Add matrix

    Set createMatrix = cMtr

    End Function

    class code for cMatrix:

    Private col As New Collection

    Public Function Count() As Long
    Count = col.Count
    End Function

    Public Function Item(ByVal NameOrNumber As Variant) As cMatrix
    Set Item = col.Item(NameOrNumber)
    End Function

    Public Sub Add(ByVal Item As Variant, Optional ByVal Key As Variant, Optional ByVal before As Variant, Optional ByVal after As Variant)
    col.Add Item, Key:=Key, before:=before, after:=after
    End Sub

  5. Hi,

    I’m writing a program in vbe and i want to save a collection for any object. I’m using this procedure:

    Public Property Get GetCargaP() As Collection
    Set GetCargaP = CargaP
    End Property

    Public Property Set SetCargaP(C As Collection)
    Set CargaP = C
    End Property

    i put after C the sintax ByRef but doesnt work (hehe’).
    Can u help me to fix this?

  6. Hi Paul,

    I am trying to add a collection(or dictionary) of the row numbers as a property to cPair in this project. I am new to classes, I tried to work it out, I think I messed it up. The data in blue color is the raw data to extract pairs and count their occurrences. It works normally. Now I wanted to add a dictionary so that the pair IDs (which are in green) are stored and displayed for each pair. Can you help me with this, please?

  7. I’ve been developing in VBA for almost 20 years and had never got into collections. But in a current project I was beating my head up against a wall trying to ReDim a two-dimensional array whose values were in one column. Finally, while scouring the web for a solutions, I came across a suggestion to use a collection and that led me here. I invested a couple of hours reviewing and documenting your course in my reference workbook, but if I don’t get all that time back on this project it won’t take long to do so.

    Thanks for all your efforts in creating a comprehensive yet concise and easily understandable course!

  8. Hello, great article, thanks for sharing with us.
    What I´m doing is copy a certain worksheet range into an array and after that store all rows into a collection. The Key is the first row entry. But after creating that collection it shows only 256 items of my 566 rows. Are collections not able to handle more than 26 items? cheers John (John Doe is no fake !!)

    1. Hi John,

      There isn’t a small limit to the collection.

      I’m guessing you are looking in the Watch window? There is a limit to what will be loaded here.

      What is the count value of the collection? That will be correct. Otherwise there is an error in how you are reading the values to the collection.

  9. Paul, I try to use your Exists() function to check whether a key exists in a collection. It works when the key is present in the collection. BUT when the key does not exist in the collection, the function does not return False. Instead an error message “run-time error ‘5’ invalid procedure call or argument” appears. I wish the function returns “False” when the key does not exist in the collection. Am I missing something?

    1. Can you post an example of your code? It’s possible that changing the key to a variant type instead of string may solve it.

  10. Great Guide.
    Please note the Before and After examples are missing the commas:
    collFruit.Add “Lemon” Before:=1
    collFruit.Add “Lemon” After:=1
    Should Read
    collFruit.Add “Lemon”, Before:=1
    collFruit.Add “Lemon”, After:=1

  11. I understand that worksheets, sheets and workbooks are collections, but if Range was a collection, wouldn’t you be able to see range.count? When I try this in my immediate window, I get an error.

    1. Hi Michael,

      You can use count of range like this

      Debug.Print Sheet1.Range("A1:B5").Count

      What error are you getting?


  12. First of all, Thank you for the reply.

    My understanding was that for all collections, you could (for example) use coll(coll.count) to access the last member of the collection. For sheets(coll.count) and worksheets this works, but for ‘range’ this doesn’t.
    I guess if you first narrow ‘range’ to a specific group of cells (as you do in your original reply) then you can treat it as a collection… but it just seems more like a ‘pseudo collection’ rather than a ‘collection’ to me.

    What am I misunderstanding, or is it actually not a “real” collection?

    1. Hi Michael,

      You can use count for range. I misunderstood your original question.

      What error are you getting when you try to use count with range?


      1. Hi Paul,

        Thanks again!

        Considering “Range” the name of the collection, when I use Range.count I get an “argument not optional” error.


  13. Where can I find pages that best covers the arguments etc for vba charts

    I want to click on a data point and then run a macro. Will use mouseover etc. The main thing at the moment what is the code to get the number and pass it to a variable. Say myTar and something like – (each time macro run on different data point)


    Once in a variable I can use it from there.

    I could not find the arguments for point to get number etc Thanks

  14. Hey Paul I am getting a 1004 error on a Range that uses a collection as a changing range
    Here is the collection:
    ‘create a collection to run through the data ranges
    Dim data As New Collection
    data.Add “ACCData” ‘these are variable namesw that hold actual ranges
    data.Add “BUSData” ‘if I remove the quotes it gives the actual range.
    data.Add “CISData”
    data.Add “HOSData”
    data.Add “PARData”
    data.Add “OACCData”
    data.Add “OBUSData”
    data.Add “OCISData”
    data.Add “OHOSData”
    data.Add “OPARData”
    Here is the part of the code where it breaks:
    Windows(myValue & “.xlsx”).Activate ‘change the F15CIS to the appropriate workbook i.e. F15ACC*****************
    Sheets(“Sheet1”).Select ‘This is the sheet you want ot get your info from*****************
    HERE IS THE BREAK Range(dept(i)).Select ‘Change this range if classes get dropped or you have re-used this code in another work book.*************
    Workbooks.Open FileName:=strPath & semester & dept(i) & “.xlsx” ‘Change the M to and F for Summer or S for spring
    Windows(semester & dept(i) & “.xlsx”).Activate ‘change the M15ACC to the appropriate workbook i.e. F15ACC*****************
    Sheets.Add After:=Worksheets(DayBefore) ‘this adds a worksheet to the end of the workbook

    I get a run time error 1004
    Method ‘Range’ of object ‘__Global’ failed

    When I put in a string to the data(i) area it works fine or if I put an actual Range (i.e. U10:Y20) It also works…

    Any Ideas?


    1. Hi Bob,

      If I understand correctly, ACCData, BusData etc. are variables.

      You cannot use them like this. If you use data.Add “ACCData” then VBA considers this a string.
      If you use data.Add ACCData then VBA will add the contents of the variable ACCData.

      Why are you trying to use a variable like this?

  15. Don’t usually leave messages on tutorials but I’ve been wasting so much time lately on terrible tutorials that it’s just a downright relief to find one so refreshingly useful. Thank you so much!

  16. For Each v In array
    If Not IsEmpty(v) Then
    If Not .exists(v) Then .Add v, Nothing
    End If
    Please could you explain me the “,Nothing” part in 3rd line: my understanding was that if ‘v’ is not empty AND is not already a key, then it will be added as new key….I first supposed “,nothing” was set to handle an ‘already existent key’ case, but I’ve verified it is not, given that when the key is existent before, I’ll get the run-time error 457.
    So when is the “, Nothing” part supposed to come into play? What does it means?

    Thank you Paul for you help!

    1. Hi Matthew,

      You haven’t shown what the .Add is belong to but it looks like a Dictionary.

      I’m guessing that the user only needs the key and doesn’t need a value so that’s why they are using “Nothing”.


  17. Hi Paul,
    Awesome write up about collections! It really helped me out.

    I just have one thing to say that you could add to here or possibly it is covered in objects section of this website.

    I was thinking that collections are always read only, but it didn’t make sense to me in terms of a collection of objects.

    I wrote the following code to test this out. I used your class write up to make a class for access and a class for excel connections.

    Sub testEditCollectionObjects()
    Dim col1 As Collection
    Dim obj1 As AccessConn
    Dim var As Variant
    Dim tbl As DAO.TableDef
    Dim DB As DAO.Database
    Set DB = CurrentDb

    Set tbl = DB.TableDefs(10)
    Set obj1 = ClassFactory(tbl)

    Set col1 = New Collection
    col1.Add obj1

    For Each var In col1
    Debug.Print var.ConnectFile ‘Prints “DATABASE=C:\BE\THETA-TRACKING_BE.accdb”
    var.ConnectFile = “Bla”

    For Each var In col1
    Debug.Print var.ConnectFile ‘Prints “Bla”

    End Sub

    The result is what is expected because the object is stored in the collection as a reference and can therefore be manipulated. I was thinking after reading this that I would have to change all my code to use a dictionary of an array but it seems a collection works fine when using objects.


    1. Hi Clint,

      You are correct. The reference cannot be changed but the object itself can.

      I will update the post about this as it has caused confusion for a few people.


  18. Hi there,
    I’ve been working my way through your articles, thanks for providing some real in-depth material. Could you let me know why I get the below message when I try to run the below code? Cheers

    Run-time ‘450’:
    Wrong number of arguments or invalid property assignment

    Sub Test()

    Dim rg As Range
    Set rg = Sheet1.Range(“A1”).CurrentRegion

    Dim i As Long
    Dim Year As Long, StartYear As Long, EndYear As Long

    StartYear = 2000
    EndYear = 2017

    Dim Row As Collection
    Dim Coll1 As Collection

    Set Coll1 = New Collection

    For i = 2 To rg.Rows.Count
    Year = rg.Cells(i, 3)

    If StartYear = Year Then
    Set rowcoll = New Collection
    rowcoll.Add rg.Cells(i, 1).Value
    rowcoll.Add rg.Cells(i, 2).Value

    Coll1.Add rowcoll
    End If
    Next i
    PrintColl Coll1
    End Sub

    Sub PrintColl(ByRef coll As Collection)
    Dim item As Variant
    For Each item In coll
    Debug.Print item
    End Sub

    1. You have a collection of collections. You need to adjust the PrintColl sub to deal with this

      Sub PrintColl(ByRef coll As Collection)
          Dim rowColl As Collection, item As Variant
          For Each rowColl In coll
              Debug.Print rowColl(1), rowColl(2)
          Next rowColl
      End Sub
  19. Paul, thanks for the great coverage. I am looking for a syntax that would let me reference the object in a collection using a variable set to the key string. I have tried this:
    Dim ClientName As New Collection
    Dim ClientParsed() As String

    ClientParsed() = Split(“Abbr;full Name”, “;”) ‘ where the string is really a variable, too.
    ClientName.Add Item = ClientParsed(1), Key = ClientParsed(0)
    MsgBox “abbr=” & ClientParsed(0) & ” name=” & ClientName(ClientParsed(0))

    When I run this, I get a “Type mismatch” error on the ClientName.Add statement. Is there a way to make this work, or do I need a dictionary?

    1. Hi Jim,

      The add line has incorrect syntax – it should use := instead of = for assigning parameters

      ClientName.Add Item:=ClientParsed(1), Key:=ClientParsed(0)


  20. Hi here is a code for filter unique values from an array (aFirstArray) and write them in column A.
    I can understand that the code ‘arr.Add a’, add all the data from ‘aFirstArray’ in to array ‘arr’.
    But how only unique values from ‘aFirstArray’ are added to array ‘arr’ with code ”arr.Add a, a’.
    Can you please explain how this code (arr.Add a, a) works so that only unique values add?

    Sub unique()
    Dim arr As New Collection, a
    Dim aFirstArray() As Variant
    Dim i As Long

    aFirstArray() = Array(“Banana”, “Apple”, “Orange”, “Tomato”, “Apple”, _
    “Lemon”, “Lime”, “Lime”, “Apple”)

    On Error Resume Next
    For Each a In aFirstArray
    arr.Add a, a

    For i = 1 To arr.Count
    Cells(i, 1) = arr(i)

    End Sub

    Thank you!

    1. You can add a key and then check if the key exists using this function:

      Function Exists(coll As Collection, key As String) As Boolean

      On Error Goto EH

      coll.Item key

      Exists = True
      End Function

      Make the key the same as the item. If the key exists already you can skip and move to the next.

      If you use a Dictionary it is easier:

      You can add items like this and if the item already exists it will only update the value and not the key:
      dict(“Banana”) = 1

  21. Hi Paul,

    In the section “adding different type” you said we have to declare sh as a variant otherwise we will get an error. I actually get it working perfectly fine with worksheet as follow.


    Sub ListSheets()

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

    End Sub

    1. Hi Paul!
      you didn’t write anything about how to put the data from a worksheet into a collection.
      I guess it is done like this
      coll.add sheet1.Range(“C5:C10”)
      but how can I later transfer this to a worksheet again?

      sheet2.Range(“A1:A5”) = coll(1)
      seems not working.
      Or are collections only for the value of a single cell? Not possible to put a row with several columns into it?


      1. A collection isn’t the same as an array. There is no way to write directly from a range to a collection.

        The collection is normally used when you are adding lots of individual items.

    1. Hi Miguel,

      The elements of a collections are variants which means that they are set when you add them.

      I am curious as to why do you need to do declare the type?


  22. Hi!
    Very nice picture of Soviet cars’ models in the header of the article.
    I had similar ones in my childhood.
    Miss them.

  23. Hello,

    This is an excellent write up, I appreciate it a lot.

    I was curious about something, let’s say we’re using code similar to this

    For Each a in CollName
    ‘if in here we wanted to check to see if something like if collname(a + 1) “some value”
    ‘is that possible?


    I did some testing in my own program and wasn’t able to get functionality like that, so I’m just curious if it exists.

  24. Hi Paul,
    Is been a great read to write macros, Now I do have my macro in an .vba/.bin file and i want to execute it node by node to my Workbook/Sheet object in java. I have been using ArrowHead ASP, tripi for some time but looking for a different third party library. What do you suggest? Shall I write my own Transpilers?

  25. Hi Paul, its me again John,
    I need to count unique ‘order numbers’ from a lot of huge flat files (up to 200.000 rows for each file). This files containing a lot of ‘colums’. First I read all lines in an array and the ‘order number’ column in another array. So far so good and fast. Now I loop through this array and count unique ‘order number’ with using dict.exists to either add the order number as key and set item:=1 or increment existing key by 1 like:
    If dict.Exists(sKey) Then
    dict(sKey) = dict(sKey) + 1
    Else: dict.Add sKey, 1: End If
    But after reaching more that 500.000 entries in the dict it slows down with each new or exisiting key.
    I want now try to use Collections but struggle how to count exisiting keys.
    I have to either remove and add with increment value or using an object for incrementing? Don´t know how to write this code and solve the performance issue. Can you help?
    Cheers John

Leave a Reply

Your email address will not be published. Required fields are marked *