Collections are a powerful feature in VBA. One of the coolest, and probably most used, functions of Collections is their ability to provide you with a unique list.
Collections are kind of like arrays, but there are some key differences. Like arrays, Collections can hold a lot of data with one variable. Unlike arrays, the data in a Collection does not have to be the same type, like using a Variant array. Also, you don’t have to allocate memory for Collections like you do for arrays. You simply add items to the collection and the memory is allocated dynamically.
A Collection object has four methods (and no properties). They are Add, Count, Item, and Remove. The Add method looks like this
Collection.Add item, [key], [before], [after]
The key argument is the key argument. What I mean by that is keys in a collection must be unique. You can use that knowledge to create your list of unique data. By assigning the key to be the same as the item, you can be sure that duplicate items will not be added. If you add a duplicate key, you will get an error. Good old On Error Resume Next will bypass that error and let you skip duplicates.
In this example, I take a list of invoices from Northwind.mdb. I want to fill a ListBox on a Userform with all the customers who have invoices. Of course a customer can have more than one invoice, so I only want the customer listed once. In a desparate attempt to use another method, I also want a count of the unique customers in a Label on the Userform. A subset of the data is below:
In the Userform’s Initialize event, I’m going to loop through column A (the customer ID) and add each customer ID to a collection. Because I’ll be making the key argument the same as the item argument, the collection will only contain each customer once. Then I’ll loop through the items in the collection, adding them to the ListBox as I go. Finally, I invoke the Count method to update the Label.
Private Sub UserForm_Initialize()
Dim cUnique As Collection
Dim Rng As Range
Dim Cell As Range
Dim sh As Worksheet
Dim vNum As Variant
Set sh = ThisWorkbook.Sheets("Sheet1")
Set Rng = sh.Range("A2", sh.Range("A2").End(xlDown))
Set cUnique = New Collection
On Error Resume Next
For Each Cell In Rng.Cells
cUnique.Add Cell.Value, CStr(Cell.Value)
On Error GoTo 0
For Each vNum In cUnique
Me.Label1.Caption = "Count: " & cUnique.Count
You may note that I use the CStr() function to convert the key value into a string. The key value can only be a string. It wouldn’t matter in this case because I’m adding strings, but it’s a good habit to form. If for no other reason than the code to add the items is in between error handling statements and you won’t see the type mismatch error you get when trying to add a non-string key. (Yes, it happens to me every single time.) The Userform looks like this: