BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog is part of our free VBA tutorial. You might find it easier to learn from our VBA courses or Excel courses.
Posted by Andy Brown on 05 March 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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
'the person class
Public FirstName As String
Public LastName As String
Property Get FullName() As String
'return the person's full name
FullName = FirstName & " " & LastName
End Property
Here's how you could create 3 instances of this class:
Sub CreatePeople()
'create 3 new person objects
Dim p1 As New clsPerson
Dim p2 As New clsPerson
Dim p3 As New clsPerson
'give them names
p1.FirstName = "Rita"
p1.LastName = "Smith"
p2.FirstName = "Sue"
p2.LastName = "Jones"
p3.FirstName = "Bob"
p3.LastName = "Brown"
'list these 3 people out
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:
'create a new collection!
Dim Persons As New Collection
You can then add items into it using the Add method:
'create 3 new person objects
Dim p1 As New clsPerson
Dim p2 As New clsPerson
Dim p3 As New clsPerson
'give them names
p1.FirstName = "Rita"
p1.LastName = "Smith"
p2.FirstName = "Sue"
p2.LastName = "Jones"
p3.FirstName = "Bob"
p3.LastName = "Brown"
'add the 3 people into the collection
Persons.Add p1
Persons.Add p2
Persons.Add p3
The advantage is that you can then loop over items in the collection easily:
'list out the people
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()
'create a new collection
Dim Persons As New Collection
'create 1 new person and a new worksheet
Dim p As New clsPerson
Dim w As New Worksheet
'give the person a name and choose a worksheet
p.FirstName = "Rita"
p.LastName = "Smith"
Set w = Worksheets("Sheet1")
'add these items into the collection
Persons.Add p
Persons.Add w
'list out the "people" !
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:
- Add and Remove methods
- Item and Count properties.
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
'this collection could contain anything, but the class
'controls access to it and ensures it will just contain people
Private Persons As New Collection
Sub Add(FirstName As String, LastName As String)
'create a new person and add to collection
Dim p As New clsPerson
p.FirstName = FirstName
p.LastName = LastName
Persons.Add p
End Sub
Property Get Count() As Long
'return the number of people
Count = Persons.Count
End Property
Property Get Item(NameOrNumber As Variant) As clsPerson
'return this particular person
Set Item = Persons(NameOrNumber)
End Property
Sub Remove(NameOrNumber As Variant)
'remove this person from collection
Persons.Remove NameOrNumber
End Sub
We can now use this class to create and list out a collection of persons:
Sub CreatePersonsCollectionSafer()
'create a new collection of people
Dim Persons As New clsPersons
'add 3 people to it
Persons.Add "Rita", "Smith"
Persons.Add "Sue", "Jones"
Persons.Add "Bob", "Brown"
'list out the people
Dim Person As clsPerson
Dim PersonNumber As Integer
For PersonNumber = 1 To Persons.Count
'list out this person (note that we need the word
'ITEM now to get at the person - can't omit this)
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:
'list out the people
Dim Person As clsPerson
For Each Person In Persons
'list out the details for each person
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
'return the collection of items
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()
'create a new collection of people
Dim Persons As New clsPersons
'add 3 people to it
Persons.Add "Rita", "Smith"
Persons.Add "Sue", "Jones"
Persons.Add "Bob", "Brown"
'list out the people (note the .Items needed)
Dim Person As clsPerson
For Each Person In Persons.Items
'list out the details for each person
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:
- Item the default property of the clsPerson class; and
- Items the default property of the clsPersons class.
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 VB.NET or C# instead ... or consider attending our online advanced VBA course (although this doesn't usually get onto creating collections).