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)
Next Cell
On Error GoTo 0
For Each vNum In cUnique
Me.ListBox1.AddItem vNum
Next vNum
Me.Label1.Caption = "Count: " & cUnique.Count
End Sub
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:
I knew I’d use this someday. Today was that day. Thanks!
And how do I iterate the keys? I have a collection with keys and values (with values being the “items”). Now I want to iterate through this collection by getting all keys and it’s values (/items) … ?
Well explained, specially the part ‘Good old On Error Resume Next’.
You can now use the ‘collections’ to compare two lists and also highlight the ‘different’ values.
Thank you!
How to retrieve values from collection using key?
Please send a reply.
This is fantastic ! Thank you so for the spending the time and effort ! I haven’t been coding for ages (+5yrs) and new something had to be better than Redim Preserve Arrays. Also the tip of the cstr is nice note too. Now I can start growing my hair back after pulling it out.
so when would you use arrays instead of collections then?
Thank you a lot. I really needed it…
>>Dim cUnique As Collection
possible there is a mistake. ‘new’ is missing:
Dim cUnique As NEW Collection
Compared to Dictionary the use of Collection is ‘suboptimal’ (to put it mildly).
That qualification also applies to the method ‘.additem’ to populate a Listbox or Combobox
Thank you so much for taking the time to explain the CStr() requirement… You are my hero!