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