Collections of class object using Excel VBA
If you've learnt how to create your own objects using classes in Excel VBA, the next step is to learn how to group them together into collections. This on-line tutorial will show you how.

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:

Bunch of bananas Different fruit
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:

3 people's names in 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:

Collection members

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:

New clsPersons class

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 instead ...

This blog has 0 threads Add post