Creating and Using Collections in VBA
Hmmm ... should I really be writing this blog? VBA isn't a proper object-orientated language (see my previous classes blog), and doesn't support collections brilliantly. However, I need closure, so ...
Although I wrote above that I needed closure, I clearly need better therapy too, as I've updated this blog (September 2014) to include extra information.
Typed and Untyped Collections
Below are examples of typed and untyped collections:
 |  |
A typed collection | An untyped collection |
As the pictures show, the difference is that you can put anything in an untyped collection, but a typed collection must contain objects of the same type.
It's better to work with typed collections, but unfortunately untyped ones are easier to create.
The Example Person Class for this Page
This page uses the following class called clsPerson as an example:
Option Explicit
Public FirstName As String
Public LastName As String
Property Get FullName() As String
FullName = FirstName & " " & LastName
End Property
Here's how you could create 3 instances of this class:
Sub CreatePeople()
Dim p1 As New clsPerson
Dim p2 As New clsPerson
Dim p3 As New clsPerson
p1.FirstName = "Rita"
p1.LastName = "Smith"
p2.FirstName = "Sue"
p2.LastName = "Jones"
p3.FirstName = "Bob"
p3.LastName = "Brown"
Debug.Print p1.FullName, p2.FullName, p3.FullName
End Sub
Running this code would give the following in the immediate window:
The output from the program above.
However, it would be nice to group the 3 people together so that you can write a loop to print out the people's names - which is where collections come in.
Creating and Using Untyped Collections
To create an untyped collection is simplicity itself - you just declare it:
Dim Persons As New Collection
You can then add items into it using the Add method:
Dim p1 As New clsPerson
Dim p2 As New clsPerson
Dim p3 As New clsPerson
p1.FirstName = "Rita"
p1.LastName = "Smith"
p2.FirstName = "Sue"
p2.LastName = "Jones"
p3.FirstName = "Bob"
p3.LastName = "Brown"
Persons.Add p1
Persons.Add p2
Persons.Add p3
The advantage is that you can then loop over items in the collection easily:
Dim Person As clsPerson
For Each Person In Persons
Debug.Print Person.FullName
Next Person
You can also show the number of objects in the collection, and remove items easily:
The built-in properties and methods for a collection.
However, you can add anything into a collection like this, with horrible results:
Sub HorribleUntyped()
Dim Persons As New Collection
Dim p As New clsPerson
Dim w As New Worksheet
p.FirstName = "Rita"
p.LastName = "Smith"
Set w = Worksheets("Sheet1")
Persons.Add p
Persons.Add w
Dim Person As clsPerson
For Each Person In Persons
Debug.Print Person.FullName
Next Person
End Sub
The code above will compile, but when you come to run it will crash when it tries to assume that a worksheet is an object of type clsPerson!
Typed Collections
The alternative - and better, if more time-consuming - approach is to create your own clsPersons collection, and define for it:
You don't have to follow the Microsoft convention - you could give your methods and properties any name you liked - but it makes sense to toe the line, I think.
First create your new class, and name it:
The new class, which will contain a set of clsPerson objects.
Here's what the code for the properties and methods listed above might look like:
Option Explicit
Private Persons As New Collection
Sub Add(FirstName As String, LastName As String)
Dim p As New clsPerson
p.FirstName = FirstName
p.LastName = LastName
Persons.Add p
End Sub
Property Get Count() As Long
Count = Persons.Count
End Property
Property Get Item(NameOrNumber As Variant) As clsPerson
Set Item = Persons(NameOrNumber)
End Property
Sub Remove(NameOrNumber As Variant)
Persons.Remove NameOrNumber
End Sub
We can now use this class to create and list out a collection of persons:
Sub CreatePersonsCollectionSafer()
Dim Persons As New clsPersons
Persons.Add "Rita", "Smith"
Persons.Add "Sue", "Jones"
Persons.Add "Bob", "Brown"
Dim Person As clsPerson
Dim PersonNumber As Integer
For PersonNumber = 1 To Persons.Count
Debug.Print Persons.Item(PersonNumber).FullName
Next PersonNumber
End Sub
Looping over typed collections
You can't loop over typed collections like this out of the box - the following code wouldn't work in the example above:
Dim Person As clsPerson
For Each Person In Persons
Debug.Print Person.FullName
Next Person
However, you can get a variation of this to work. The first thing to do is to add one more property to our collection class:
Property Get Items() As Collection
Set Items = Persons
End Property
What this will do is return a set of all of the items accumulated in our collection, which we can then loop over by adding .Items to the end of our collection:
Sub CreatePersonsCollectionSafer()
Dim Persons As New clsPersons
Persons.Add "Rita", "Smith"
Persons.Add "Sue", "Jones"
Persons.Add "Bob", "Brown"
Dim Person As clsPerson
For Each Person In Persons.Items
Debug.Print Person.FullName
Next Person
End Sub
Programming nirvana - almost! It would be so nice to be able to remove the .Item and .Items references in our code ...
Removing the need for Item references
To make our collections perfect, you could make:
Doing this would remove the need to suffix code with .Item and .Items. So for example you could write in the above loop the simpler:
For Each Person In Persons
To do this, you need to add hidden attributes to the code. This is a messy and intricate business, as the excellent comments from David Sondergaard below show. If you really want to get this to work, visit this blog.
Personally, I wouldn't attempt to do this: the solution is messy and flaky, and to me the problem just doesn't seem worth overcoming. You could always upgrade to C# instead ... or consider attending our advanced VBA course (although this doesn't usually get onto creating collections).
