The Ultimate Guide To Collections in Excel VBA

VBA Collection

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

 

A Quick Guide to Collections

TaskExamples
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 Items
(Dim coll As New Collection)
Set coll = Nothing
coll.Add "Apple"
Remove all Items
(Dim coll As Collection
Set coll = New Collection)
Set coll = Nothing
Set coll = New Collection
coll.Add "Apple"

 
 

Introduction

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

 
 
The following code shows some examples of using the VBA Workbooks collection

    ' Workbooks is a collection of all open workbooks

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

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

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

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

 
 

What is a Collection?

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

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

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

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

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

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

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

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

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

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

    ' Create collection
    Dim collMarks As New Collection

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

 
 

Collections Vs Arrays?

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

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

 
 

Example: Where an Array is Better

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

 
 

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.

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

 
 
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 the of students with a given criteria. For example only the students from the USA or England that study Maths or History. In other words you will not 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

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.

 
 
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
    Endif

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

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

 
 

Removing All items from a Collection

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

    Set Coll = Nothing

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

 
 

Declaring Using New

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

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

The following code demonstrates this.

Sub EmptyColl()

    ' Create collection and add items
    Dim coll As New Collection

    ' add items here

    ' Empty collection
    Set coll = Nothing

    ' Add item
    coll.Add "Pear"

End Sub

 
 

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

 
 

Using Set and New

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

Sub EmptyCollSet()

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

    ' Add items here

    ' Empty collection
    Set coll = Nothing

    ' SET TO NEW BEFORE USING
    Set coll = New Collection

    ' Add item
    coll.Add "Pear"

End Sub

 
 

Remove All – An Alternative Method

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

Sub RemoveAll(ByRef coll As Collection)

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

End Sub

 
 

Adding items to a Collection

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

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

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

   collTotals.Add 45.67
   collTotals.Add 34.67

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

 

Before and After

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

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

 
 
After this code the collection is in the order
1. Lemon
2. Apple
3. Pear

 
 

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

 
 
After this code the collection is in the order
1. Apple
2. Lemon
3. Pear

 
 

Accessing Items of a Collection

To Access the items of a collection you simply use the index. As we saw the index is the position of the item in the collection based on the order they were added.

 
 
The order can also be set using the Before or After parameter.

Sub access()

    Dim coll As New Collection

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

    ' Will print Apple
    Debug.Print coll(1)

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

    ' Will print Orange
    Debug.Print coll(1)

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

End Sub

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

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

 
 

Items in a collection are Read Only

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

Sub WriteValue()

    Dim coll As New Collection

    coll.Add "Apple"

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

End Sub

 
 

Adding different types

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

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

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

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

Sub ListSheets()

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

End Sub

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

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

 
 

Adding Items Using a Key

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

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

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

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

The following code shows a second example of using  keys

Sub UseKey()

    Dim collMark As New Collection

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

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

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

End Sub

 
 
Using keys is has three advantages:

  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.

 
 

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.

 
 
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

    coll.Item key
    
    Exists = True
EH:
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 not so easy to get around unless you have a good knowledge of programming.

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 postion one and the last item is always in the position specified by the Count property of the collection.

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

Sub UserCollection()

    ' Declare and Create collection
    Dim collFruit As New Collection

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

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

End Sub

 
 

Using the For Each

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

Sub AllWorkbookForEach()

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

End Sub

 
 
The format of the For loop is:
For i = 1 To Coll.Count
Next
 
where i is a long and Coll is a collection.

 
 
The format of the For Each Loop is:
For Each var In Coll
Next
 
where var is a variant and Coll is a collection.

 
 
To access each the item
For: Coll(i)
For Each: Var

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


Sub UseBothLoops()

    ' Declare and Create collection
    Dim collFruit As New Collection

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

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

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

End Sub

 
 

For Each Versus For

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

The For Each Loop

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

 
 
The For Loop

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

 
 
Let’s  compare the loops under each of these attributes

 
 

Speed

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

 
 

Neater

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

Sub PrintNamesFor()

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

End Sub

Sub PrintNamesForEach()

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

End Sub

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

 
 

Order

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


Sub ReadRightToLeft()

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

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

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

End Sub

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

 
 

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

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

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.

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.

 
 

Conclusion

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. Collections 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?

If you want to read about more VBA topics you can view a complete list of my posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA.

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

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.

Free VBA eBook

 
 


 
 

53 comments

  1. Are you sure your last example is correct? Should Line 18 be:
    Set CreateCollection = coll ?
    Also, Line 6 creates coll but doesn’t either return it or display it.
    Am I missing something?

    1. Hi Ronald,

      Well spotted. Line 18 was a typo. I updated the code.

      Line 6 is correct. Just to clarify. The function CreateCollection creates the collection. It passes this back to coll.

      In normal code you would do something once you create the collection. The purpose here was to show how to pass it back.

    1. Hi Sam,

      It’s already in a collection. It’s part of the worksheet shapes collection.

      You can go through all the dropdowns(i.e. comboboxes) on a worksheet like this

      [sourcecode language=”vb”]
      Dim s As Variant
      For Each s In ActiveSheet.Shapes
      If s.Type = msoFormControl Then
      If s.FormControlType = xlDropDown Then
      Debug.Print s.Name
      End If
      End If
      Next
      [/sourcecode]

      1. Paul, thank you for your reply. I have 60 ActiveX ComboBoxes on a worksheet, but this line of codes “If s.Type = msoFormControl Then” did not even recognize any of them.
        What I’m trying to do is I want to divide the 60 ComboBoxes into a few collections/groups of ComboBoxes, and then use “For Each loops” to loop through each collection/group accomplish some tasks. I would greatly appreciate another set of example codes that you could help me with.
        By the way, when do you think you will finish your new book?
        In my perspective, you are the best VBA teacher on the internet.

        1. The new book(Build 11 VBA Applications) will be available to people who preordered on Dec 20th. It will be available for everyone in early 2016.

          The code I wrote was for a normal combo box which is a msoFormControl. ActiveX combos are different. They are msoOLEControlObject. You can list them all like this

          [sourcecode language=”vb”]
          Sub ListAllActiveXCombos()

          Dim s As OLEObject
          For Each s In Sheet1.OLEObjects
          If InStr(s.progID, "Forms.ComboBox") > 0 Then
          Debug.Print s.Name
          End If
          Next

          End Sub
          [/sourcecode]

          If you group the comboboxes you can go through them like this

          [sourcecode language=”vb”]
          Sub ListCombosInGroup()

          Dim s As Shape, oOle As OLEObject

          For Each s In Sheet1.Shapes("Group 1").GroupItems
          Set oOle = s.OLEFormat.Object
          If oOle.progID = "Forms.ComboBox.1" Then
          Debug.Print s.Name
          End If
          Next

          End Sub
          [/sourcecode]

  2. Hi Paul,

    Thanks for this excellent guide. Concise and practical, whilst having enough information for a proper understanding.

    I’m new to VBA and am wondering which would be the best solution for the following problem:

    We have a sheet with a list of adverts and the following captions “Advert ID”, “client name”, “view event”, “click event”, which respectively hold [on a per row basis] an ID number for the advert, a number for the client, time stamp for the view and time stamp for the click.
    The script needs to loop though each row and count views and clicks.

    I have generated a User Defined type as follows:
    Type Advert
    ID As Integer
    Client As Integer
    ViewCounter As Integer
    ClickCounter As Integer
    End Type

    I’m not sure about using arrays or collections

    #1 Array
    Need to dimension the array accordingly, and for each row:
    a. check whether another record of the same advert has been already processed in the array. i.e. Is there a record with an ID stored in Advert.ID equal to the ID being looked up?
    a.1 if yes, increase event counter or view counter by one
    a.2 if no, find the next empty slot and fill it up with Advert.ID, Advert.Client and View and/or Click counter with a value of 1

    #2 Collection
    Based on your guide, it looks as a much neater solution to me. However, not sure whether you can use it with a user defined type. If it is read-only, it is possible to update the ViewCounter and ClickCounter on the fly, as the script loops through records?

    Thanks,
    Alex

    1. Hi Alex,

      You best bet here is a Dictionary rather than an Array or Collection.
      A Dictionary allows you to store a unique key and an item. You can check if the item exists and if so update the value.

      I’m actually writing a blog post about the dictionary at the moment. It should be available in the next week. In the mean time here is some sample code

      Sub UseDictionary()
      
          ' Tools-References and check the box beside
          ' Microsoft Scripting Runtime
          Dim dict As New Scripting.Dictionary
          
          ' Add key and values
          dict("Apple") = 1
          dict("Pear") = 1
          dict("Orange") = 1
          
          ' Check if this fruit type exists
          Dim sFruit As String: sFruit = "Pear"
          
          If dict.Exists(sFruit) Then
              ' Exists so add one to value
              dict(sFruit) = dict(sFruit) + 1
          Else
              ' Doesn't exist so add new fruit to dictionary
              dict(sFruit) = 1
          End If
          
          ' Print out the dict items
          Dim key As Variant
          For Each key In dict.Keys
              Debug.Print key, dict(key)
          Next
          
          ' Clean up
          Set dict = Nothing
          
      End Sub
      
  3. Hi Paul

    Thanks for this very detailed article. I still have one question. Can I add a range of unique values to a collection at once instead of writing Add “” multiple times with VBA?

    thanks

    1. Hi Jake,

      No, you have to use multiple adds.

      You can add a range of items to an array at once e.g.

      Dim arr As Variant
      arr = Range("A1:Z100").Value
      

      Regards
      Paul

      1. Hi Paul,

        Thank you very much for you quick reply and the help.

        This is a fantastic blog for VBA users and so is the book available for download, great job!

      2. Hi Paul,

        I love your work!

        Following up on Jake’s question, I do not see where a collection is an advantage, particularly in regards to your explanation in “Example where a Collection is better”. What is the advantage if you are having to write 50 add statements to add 50 students to the collection when you can write one statement setting the array? I understand the array is a fixed size but it can be resized on demand. The collections size is only defined by the number of items in it but you have to add each item to it individually. Doesn’t seem like much of an advantage to me but I am kind of new to this.

        Thanks for writing this stuff down in such an understandable manner!!
        Marc

        1. Thanks Marc,

          Glad you like the blog.

          It is a good question you ask because the differences can appear subtle.
          You would never add using 50 statements. Rather you would use this in a loop.

          With a Collection it is simple to add an item to any position or to delete any item from a position.

          With an array this is really difficult. Even a simple add with an array requires two lines. One to resize and one to set the value.

          Imagine you were reading 1000 records and adding based on a given criteria. You don’t know the number items in advance.
          Using the array you would need to resize and set the value each time you meet a record with the correct criteria.
          With a collection you can just add as you need. VBA looks after the resizing.

          ' add to collection
          c.Add "Pear"
          
          ' add to array
          ReDim arr(UBound(arr) + 1)
          arr(UBound(arr)) = "Pear"
          
  4. Hi Paul,
    My compliments on your website, it is excellent.

    I have a question regarding memory leakage.
    Is this something we should be concerned about i.e. after we have created (New Collection) and then used a collection should we always set the collection equal to Nothing (set coll=Nothing)?
    Perhaps you have already commented on this, and if you are repeating yourself, my apologies.
    Regards
    Tom

    1. Thank’s Tom,

      It is good practice to set objects to nothing when finished with them just to be on the safe side.
      In reality, the only time it becomes an issue is you are dealing with a large number of items and then only in specific cases.

  5. I tried to use collections and I can add values to the collection no problem using a function, but when I try and access items I get a ‘subscript out of range error’ on the line referencing the collection. The variable is declared at the top of the same module and I have an if statement checking it isn’t nothing. I’m at a bit of a loss!

    If LayerBoundaries Is Nothing Then
    Exit Sub
    Else
    With ActiveLayerBoundaries
    For i = 1 To ActiveLayerBoundaries.Rows.Count
    .Cells(i, 1).Value = LayerBoundaries.Item(i) <ERROR!
    Next
    End With
    End If

    I'd be very greatful for any ideas.

    1. OK..for some reason I cant programmatically reference the collection item index, when I substitute i for 1 in the loop I get the first result written to the whole range.

    2. Hi Joseph,

      You are using i for the number of rows and for the current collection item. It looks like there are more rows than collection items which is why you get the “subscript out of range error”. This error means there is not an item at the position you are currently checking. Try replacing your For loop with this one

      [sourcecode language=”vb”]
      For i = 1 To LayerBoundaries.Count
      [/sourcecode]

  6. Excellent article on Collections. Two minor quibbles, though. To ascertain if a collection item exists, use a Key for each item. By using “On Error Resume Next”, this can filter out duplicates or, if you want to do something different if the item exists, check for error 457.

    Also, if one creates a collection of User Defined Objects, one can change a property of the UDO within the collection. I have read that for some sizes of data, Collections can be faster than Dictionaries, but I have no practical experience with that, myself.

    I frequently combine those two features in order to add an item to a collection within a UDO, where the UDO is already in a Collection.

    1. Hi Ron,

      Thanks for your comments. Yes, you can use “On Error”. I’ve been meaning to add this Exists function to this post. I will update it shortly.

      You can change the property of any object in a collection and not just user defined. This because the collection doesn’t store a copy of the item it stores a pointer to the object.

      Regards
      Paul

  7. Hi Paul,

    thanks for this fantastic blog!

    Please take a look at example Sub UseColl()
    It seems that line 20 should be Debug.Print item instead of call.

    Marco 🙂

  8. Hi
    Great stuff on this site. I have a question, having populated a collection with integers, is it possible to find the highest (Max) value ?
    Thanks

    1. Hi JCP,

      You can find the max value using the GetMax code below

      Sub ExampleUse()
          
          Dim coll As New Collection
          coll.Add 15
          coll.Add 34
          coll.Add 11
          
          Debug.Print GetMax(coll)
          
      End Sub
      
      ' Return max value in given collection
      Function GetMax(ByVal coll As Collection) As Long
      
          Dim item As Variant, lMax As Long
          For Each item In coll
              If lMax < CLng(item) Then
                  lMax = CLng(item)
              End If
          
          Next
          
          GetMax = lMax
      
      End Function
      
  9. Hi Paul,

    One remark regarding collections.

    In the ‘Sorting a collection’ chapter, Sub testSort() should the number 2 be removed to use it with the sort routine.
    Set coll = SortCollection2(coll) should be
    Set coll = SortCollection(coll)

    Best regards,
    Ludo

  10. “The For Each Loop
    •is faster”

    I wondered, given that I’d always understood the opposite, whether you were right about this.

    Turns out,

    “Using For Each to Loop Through Arrays Is Not Recommended” (source https://support.microsoft.com/en-us/kb/129931)

    While this article is, strictly speaking, VB, not VBA, the same logic applies

    1. Hi Matt,

      Thanks for your comment. I think you are confused here. My post is referring to using For Each with Collections not arrays.

      Your article is only referring to using For Each with non-variant arrays e.g. “You can also use the For Each statement with an array that is not a variant type, but the result is a substantial decrease in performance”.

      If you want to try it out yourself it is easy to create some test code that compares the speed of For Each versus For.

  11. Saying items in a collection are read only is not entirely true, you can still change property values of items in a collection:

    For a class called Employee with two properties, Name and Salary, the following code works just fine:

    Sub collectionTest()
    Dim Employees As Collection
    Dim Emp As Employee

    Set Employees = New Collection

    Set Emp = New Employee
    Emp.Name = “Bob”
    Emp.Salary = 40000

    Employees.Add Emp

    Employees(1).Name = “Frank”
    Employees(1).Salary = 50000
    End Sub

    However, if at the end “Set Employees(1) = Emp” is added, then it throws an error.

    1. Hi Joe,

      Thanks for your comment. You raise an interesting point here. Let me explain it.

      When you add something to a collection, VBA creates a copy of the variable. This variable is read-only and cannot be changed.

      When you create an object using New, VBA creates the object(e.g. Employee) in memory and stores the memory address in a variable(e.g. Emp). See VBA Objects in Memory.
       

      ' memory Address of new object stored in Emp
      Set Emp = New Employee
      

       
      When you add Emp to the Collection, VBA creates a copy of the variable. That is, it creates a variable with the same memory address of the object.

      So the actual object is not stored in the Collection – rather a variable with the address of the object is stored. This variable is read-only but the object it is pointing to isn’t.

      Hope this makes sense,
      Paul

  12. This is my first go-to site whenever I need help with VBA. Great work!
    I’m currently working on two separate workbooks(both with large no. of rows and columns, typically 1st column related to next and so on) wherein I need to copy customer name from a cell in the leftmost column and then use it to search the same in the other workbook. I intend to use the copied name to filter the data in the latter book and then narrow the results. This process will happen 4 times for 4 columns after which, copy-paste of the rest of data from sheet 2 to sheet 1 takes place.
    I’m opening the workbooks, copying the first cell, moving to second wbook but when I try to search with it, it doesn’t filter. I’ve tried using Range.Filter and .Autofilter but none has worked. Please suggest something.
    Cheers.

    1. Hi Junaid,

      I’m not exactly clear what your issue is. I would normally use an array to search rather than a filter.
      It’s hard to see your problem with out the code. You can email me the code at paul@ExcelMacroMastery.com and I’ll have a look at the code if I get time.

  13. Another note for removing all items from a collection: you don’t need to set the collection to Nothing first. Regardless of how you created the collection, you can simply use “Set coll = New Collection” at any point to remove all items and leave you with an empty collection that is ready for immediate use.

  14. Paul
    Beautifully explained. Thanks for sharing your knowledge and expertise in VBA. I’ve been able to generate a collection, as best I can tell from a MsgBox
    For i = 1 To TRIFIDS.Count
    Cells(i, 1) = TRIFIDS(i)
    Next i
    MsgBox Cells(22, 1)
    Now that I have the collection, how does one manipulate those data? I want to check for duplicates, then check for unique values. What if more than one column of data is involved? Hope this is not too general of a question? Most appreciate….Happy Holiday Wishes from the U.S. Mort

  15. Merry Christmas and Happy New Year Paul

    Is there a straight forward to put all items of a collection back into a range of cells without using loop?
    For arrays, we can simply use the following code to do the job:
    Dim Students(3) As String
    Range(“A1:D1”) = Students

    But I if I create Students as a collection, the code won’t work? Why is that?

    1. Hi Patrick,

      An array is essentially a group of sequential variables in memory. This is why VBA can easily do a copy from the ranges to the array.
      So an array is good for adding a lot of items at once. Adding and removing single items is much more complex.

      A collection is essentially a linked list in memory. This type of structure is suitable for adding and removing single items but not so good for adding lots of items at one time.

      Happy New Year
      Paul

  16. Hello Paul
    Still can get my head around the stuff about adding, lets say i have like 100 different customers, then i need to write Coll.Add “Customer X” for each customer?

    Not possible to adress the collection to the range where all the information are stored and let the collection add these?

    1. Hi Peter,

      You can write a loop to read a range into a Collection but you do need to use Coll.Add for each item. A Collection is more suitable for adding and removing items one at a time.

      An array is more useful for adding a lot of items at once. It’s not so good for adding/removing single items.

      Regards
      Paul

  17. Hi Paul,
    How can we change the values of an arrays elements.

    We either define the array elements in our code, like
    arr = Array(“A”,”B”,”C”)
    or can pass the address of cells having the data.

    So how it is possible to change the value of an array elements.
    Is it something like, arr(1)=”New Value”

    Thank You.

  18. Hi Paul,
    I just found fantastic articles that’re your posted blogs.
    It is very helpful for newest VBA users like me.
    I would ask you how I can share the collection to the end users via MsgBox, but do Not create additional objects like oWord or oExl.sheet?
    Simply, I would just show to user the completed Immediate window where all collection is stipulated already.
    If the completed collection is too big, so the information can be shown to end users via creating object like text.doc (which is mach less in bytes compare to oWord or oExl.book or other excel arrays(sheets)).

    This can be handy if no extra code is needed for further programming to work with collection.

    Thank you

    1. Hi Valery,

      I not 100% clear on your question. You want to display the contents of a Collection to the user using a dialog? You can create a UserForm with a textbox and then write the collection to this TextBox.

      See posts on Userforms here and on UserForm controls here

Leave a Reply

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