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 | 4 comments

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 4 comments

Comment added on 03 July 2012 at 07:38 GMT
Thanks for the blog, helped clarify a few things for me....

(also, just a query on the Dim Person as clsPerson in the middle of the last example.... I don't think that is required as no further reference seems to be made to that var beyond that point.  The code still executes without that statement).

Thanks again!
Comment added on 11 April 2013 at 19:43 GMT
This example helped me so much.  I really just wanted to thank you for the help.
Comment added on 11 September 2013 at 09:49 GMT
While you are unable to edit the properties of an excel class via the VBE, you are able to export the class as a cls file and edit it as a text document.  This is where the ability to loop comes in.

Create a class module as you normally would, and then after creating it export it to your desktop.  Open it up and place the following code at the top:

------------------------------------------------------------

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END

'-------------whatever the name of your class is
Attribute VB_Name = "clsLOPCEntries"              
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = objPeople.[_NewEnum]
End Property

-------------------------------------------------------------------------------

The above property will be activated whenever you try and loop through a collection class.  This is how you do it!

So now you can do People.OutputEntries and within your clsPeople have the sub OutputEntries (for each Person in People print Person.Firstname).

You will not need to have the specific index number, etc.  

This will also allow you to create classes within classes.

E.g. People.GetPeopleInCity("New York").FilterByLastName("Sondergaard").OutputEntries

BEAUTIFUL!

This is how I create relational databases within excel forms ( I will create 20 some odd classes and collection classes and nest them in each other.  for example I could have a collection class called People, a person class that is loaded into People, but I can also instantiate a people in Person to allow recursive code and build family trees, etc.)  People like my relational excel databases because while access is still restricted one person at a time (for integrity), it allows people to see the data relationally and without the need to pay for the Access or other licenses.

Based on that example, I can keep on drilling into the data to find out the lineage of a person upward or downward depending on how the code is structured.
Comment added on 19 September 2013 at 06:44 GMT
Hello Everyone,

I have tried to use the 

Version 1.0 Class
Begin
MultiUse = -1 'True
End

workaraound and when I import the .cls file into the vbe 

The class module starts with 

Multiuse = -1 'True <----------------- THIS TEXT IS IN RED
End

I have no idea why but it is very frustrating that so many people can pull this trick off but I just can't get it to work.

Tanks 

Jason

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.