Using collections in VBA

When working in languages such as Java or C#, collections are a very basic part of your toolbox. The reason you want to work with collections rather then arrays is that you want to avoid having to resize your arrays all the time. Arrays are basically only useful as long as you know, in advance, the size of your data collection. If you don’t, you will have to resize it regularly to adapt to the data.
Of course, the resizing will have to be done anyways, but preferably not by you. Most modern languages have built in collection types (List in Java, Collection in VB and ArrayList in C #) and they are easier to use than creating a class of your own. The VBA collection is the same as the VB collection with the syntactical changes that comes with the languages (more info on the changes between the VBA and VB Collection can be found here).

To create a collection of names you write this:

    Dim names As New Collection

This creates a new collection object with which we’ll work and it has 4 methods: Add(), Count(), Item() and Remove(). These ought to be quite simple to figure out so I won’t go any further into them.

Now, I’ll simply show you an example of when I had to use a collection as examples are a perfect way of understanding the use of things.
The issue at hand was an excel sheet with data like this:

Our example data
Our example data

What I want to do is get a list of all unique names of people who have spent money on my site. This is of course a silly example but it will have to do. I only want the people who have actually spent money and I don’t want duplicates. This is a bit tricky to get done using only simple functions so I thought I would do a sub routine in VBA for it. I will show you the code, which is commented, and then make some remarks afterwards:

Sub FindGoodCustomers()
    'Declase variables
    Dim names As New Collection
    Dim nameIndex As Integer
 
    'Select Sheet1
    Sheets("Sheet1").Select
    'Select cell B1
    Range("B2").Select
 
    'Loop through all the cells in column B
    Do Until Selection.Value = ""
 
        'We only care about the people who spent moneh
        If Selection.Offset(0, 1).Value > 0 Then
            'Call our adding routine
            Call AddUniqueToCollection(names, Selection.Value)
        End If
 
        'Move down one step
        Selection.Offset(1, 0).Select
    Loop
 
    '====SWITCH SHEET AND OUTPUT ALL NAMES====
    Sheets("Sheet2").Select
    Range("A1").Select
 
    For nameIndex = 1 To names.Count
        Selection.Value = names.Item(nameIndex)
        Selection.Offset(1, 0).Select
    Next nameIndex
 
End Sub
 
 
 
'A routine to add a name to the collection ONLY
'if it is not already in it
Private Sub AddUniqueToCollection(names As VBA.Collection, name As String)
    Dim nameIndex As Integer
 
    For nameIndex = 1 To names.Count
        If (names.Item(nameIndex) = name) Then
            Exit Sub 'This means the name is already in this sub, so stop it
        End If
    Next nameIndex
 
    'The name was not found, add it to the collection
    names.Add (name)
End Sub

I use two sub routines, FindGoodCustomers and AddUniqueToCollection. The FindGoodCustomers routine is the one we actually call. It will create a collection of names and then loop through the list of names in Sheet 1, column B starting on the second row. We will only bother to add customers who’s actually bought something so we check the column next to the name, which is the column containing how much money they’ve spent, to see if they’ve spent anything at all.
If they have spent money we use our other routine to add them to the list. The AddUniqueToCollection routine just loops through the list making sure the new name is not in it. To stream line it (as far as stream lining could be done in VBA) we stop immediately after we find a match.
After creating the list we output it in Sheet 2, this could of course be done anywhere, even in an external text file or something.

Hopefully this will be useful for somebody somewhere…

3 thoughts on “Using collections in VBA

  1. Nice post, but your “AddUniqueToCollection” is procedural overkill at this point. One of the nice things about Collections is that they allow you to index contents by name. So you don’t need to check for uniqueness, just simply add the name to your collection and use the name as both the index and the value. Duplicates will just overwrite themselves. Much less expensive than going through that loop, and eliminates the need for that sub.

Leave a Reply