- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
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.
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 ...
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
Yuk! Not only can we not loop over the objects in the collection (instead, we have to get at them by number), but we also have to specify the Item keyword explicitly. There is no perfect solution, it appears!
Comments on this blog
This blog has 2 comments: