Collection Object
The Collection object is particularly well suited for class programming. However, it can be used only in normal modules and is often a convenient alternative to fields. It makes possible the definition of custom lists (enumeration objects). You can thus use the same mechanism that is so often used in the Excel library for listing objects (Workbooks, Windows, etc.).
It is very easy to use the Collection object. You must generate a new object of type Collection using Dim New. Then you can add, with the Add method, variables, fields, object references, and even further Collection objects to the list. In contrast to fields, elements of a Collection can be of differing types.
As a second parameter you must give a character string to be used as a key to accessing the object. This string must be unique, and so may not coincide with an already existing string. As with variables, there is no distinction between uppercase and lowercase letters for keys.
Dim c As New Collection
c.Add entry, "key"
Objects are accessed just as with all listings: by giving an index value (between 1 and c.Count) or by giving the character string that was used with Add as a key. With the property Count you can determine how many elements the listing contains. With Remove you can again remove individual objects.
Dim c As New Collection
c.Add "a character string", "abc"
c.Add 123123, "def"
Debug.Print c.Count ' returns 2
Debug.Print c.Item(1) ' returns "a character string"
Debug.Print c(1) ' as above (Item is the default method)
Debug.Print c("def") ' returns 123123
In the above example "abc" and "def" are the keys with which the elements can be accessed. If you use an already employed string as a key for a new element, then you will receive error 457: "This key is already associated with an element of this collection." As expected, the elements of a collection can be addressed in a For–Each loop. Then element has the type of the current element. If you save data of various types in a Collection, then you must establish the type with TypeName and provide a corresponding case distinction.
Dim element As Object
For Each element In c
...
Next
The Dictionary object is a competitive alternative to the Collection object. It makes possible a subsequent alteration of existing entries and makes some additional methods available. (Note, however, that even with identically named methods of Collection and Dictionary there is sometimes a different ordering of the parameters. You can thus not automatically convert existing code from Collection to Dictionary.)
Note / The Dictionary Object is not defined in the VBA library, but in the Scripting library. In order to use Dictionary you must activate the library Microsoft Scripting Runtime with TOOLS|REFERENCES.Example for a Class Module
This example consists of the two class modules XYPoint and XYPoints of the statistical library mentioned above. The class XYPoint proves that a class can be defined without creating a large amount of code. The two global variables x and y represent the only data elements of the class (a two-dimensional point). Access is accomplished directly (without the detour via property procedures) by means of objectname.x and objectname.y.
' File VBA-Concepts.xls, Class "XYPoint"
Public x As Double, y As Double
There is more interesting code to be found in the enumeration class XYPoints, which serves both for managing a number of XYPoint objects and for their statistical evaluation. The management of data is accomplished by means of the local variable points, which refers to a Collection object that is generated automatically for every new XYPoints object.
The Add method makes possible the addition of a new point to the list. For this purpose an XYPoint object is generated and x and y stored within it. Then this object is added to the Collection points. The method returns the new XYPoint object.
The implementation of the Count property is extremely simple: It must simply be passed back to the like-named property of the Collection object points. The property procedure is defined to be read-only. It would make no sense to change the Count property.
In the property procedure XMean (also for read-only access) the mean value of all x values of all stored XYPoint objects is calculated and returned.
' File VBA-Concepts.xls, Class "XYPoints"
Private points As New Collection
Public Function Add(x As Double, y As Double) As XYPoint
Dim xyp As New XYPoint
xyp.x = x
xyp.y = y
points.Add xyp
Set Add = xyp
End Function
Property Get Count() As Integer
Count = points.Count
End Property
Property Get XMean() As Double
Dim p As XYPoint, xm As Double
If points.Count = 0 Then XMean = 0: Exit Property
For Each p In points
xm = xm + p.x
Next
xm = xm / points.Count
XMean = xm
End Property
Example for Derived Classes (Implements)
The purpose of this example is first to define a superclass Document and then two classes derived from it, Book and Magazine. Since the example is relatively complicated, it has been included in its own example file (Implements.xls). To try out the code, in the development environment launch the procedure Test_Classes in the module TestClasses. As a result, four lines are output in the immediate window:
Title: Linux
Publishing year: 1/1/1999
Title: Linux Magazine 1/2000
Publishing year: 1/1/2000
Application of the Object Classes
Before the code for these classes can be explained, we have a few words to say about the application of these classes. In the following lines two objects of type Book and Magazine are initialized. The only thing that is really interesting is, in fact, the call from Print_Info. To this procedure, whose only parameter is declared as Document, are passed a Book object the first time and a Magazine object the second. That is possible syntactically only because both Book and Magazine are based on the same superclass Document.
' example file Implements.xls, Module "TestClasses"
Dim mybook As Book
Dim mymagazine As Magazine
Private Sub Test_Classes() ' Execute this procedure with F5!
Init_Data
Show_Data
End Sub
Private Sub Init_Data()
Set mybook = New Book
mybook.Title = "Linux"
mybook.PublishingYear = #1/1/1999#
mybook.Author = "Kofler, Michael"
Set mymagazine = New Magazine
mymagazine.Title = "Linux Magazine 1/2000"
mymagazine.PublishingYear = #1/1/2000#
End Sub
Private Sub Show_Data()
Print_Info mybook
Print_Info mymagazine
End Sub
Private Sub Print_Info(x As Document)
Debug.Print "Title: " & x.Title
Debug.Print "Publishing year: " & x.PublishingYear
End Sub
The Superclass Document
There is nothing special about the class module Document. Within it are defined the two properties PublishingYear and Title as well as the method ShowInfo.
' example file Implements.xls, Class Module "Document"
' two properties: Title, PublishingYear
' one method: ShowInfo
Private docYear As Date
Private docTitle As String
Public Property Get PublishingYear() As Date
PublishingYear = docYear
End Property
Public Property Let PublishingYear(ByVal date As Date)
docYear = date
End Property
Public Property Get Title() As String
Title = docTitle
End Property
Public Property Let Title(ByVal title As String)
docTitle = title
End Property
Public Sub ShowInfo()
MsgBox "Title: " & docTitle & ", year of publication: " & docYear
End Sub
The Derived Class Book
With the line Implements Book the class Book is derived from Document. This means that all the methods and properties of Document must be defined in Book in exactly the same way.
In order to use already existing code from Document we will need to do a bit of juggling. First of all, within the Book class an object of type Document must be generated. For this the event procedures Class_Initialize and Class _Terminate will be employed.
' example file Implements.xls, class module Book
' three properties: Title (of Document)
' PublishingYear (of Document)
' Author (new)
' one method: ShowInfo (of Document)
Implements Document
Private mydoc As Document
Private bookAuthor As String
Private Sub Class_Initialize()
Set mydoc = New Document
End Sub
Private Sub Class_Terminate()
Set mydoc = Nothing
End Sub
Second, procedures for all events and methods of Document must be newly implemented. Here, however, you may make use of the events and methods of the mydoc object. Note that the names of the procedures are composed of the superclass (that is, Document) and the name of the property or method.
' Code for the properties of Document
' (relies on the Document properties)
Private Property Get Document_PublishingYear() As Date
Document_PublishingYear = mydoc.PublishingYear
End Property
Private Property Let Document_PublishingYear(ByVal date As Date)
mydoc.PublishingYear = date
End Property
Private Property Get Document_Title() As String
Document_Title = mydoc.Title
End Property
Private Property Let Document_Title(ByVal title As String)
mydoc.Title = title
End Property
Private Sub Document_ShowInfo()
mydoc.ShowInfo
End Sub
Third (and this is hardly believable!), you must now make the Document properties available to the Book object as well. (The second step had only the function of being able to use the Document code further internally. Therefore, the procedures were declared as Private.)
' Code, to make the Document properties available to
' the book object as well
Public Property Get Title() As String
Title = Document_Title
End Property
Public Property Let Title(ByVal title As String)
Document_Title = title
End Property
Public Property Get PublishingYear() As Date
PublishingYear = Document_PublishingYear
End Property
Public Property Let PublishingYear(ByVal date As Date)
Document_PublishingYear = date
End Property
Public Sub ShowInfo()
Document_ShowInfo
End Sub
This means the following: For each property from a superclass that you wish to use in the future unchanged, you need four (!) procedures, while for each method you still need only two.
At long last, the Book class should be extended by the additional property Author.
' code for the additional property
' (specific to the Book object)
Property Get Author() As String
Author = bookAuthor
End Property
Property Let Author(author As String)
bookAuthor = author
End Property
The Derived Class Magazine
The class Magazine as well is derived from Document, and this class, too, was extended with an additional property, called Articles. In contrast to Book, the properties Title and PublishingYear were newly implemented, in order to demonstrate a second method of programming derived classes. For this reason, no mydoc object is needed (as in Book).
' example file Implements.xls, Class Module "Book"
' three properties:
' Title (Definition as in Document, but newly implemented)
' PublishingYear (Definition as in Document, but newly implemented)
' Articles (new)
' one method:
' ShowInfo (Definition as in Document, but newly implemented)
Option Explicit
Implements Document
Private magazineYear As Date
Private magazineTitle As String
Private magazineArticles As String
' code for the properties and methods from Document
' (newly implemented for this class)
Private Property Get Document_PublishingYear() As Date
Document_PublishingYear = magazineYear
End Property
Private Property Let Document_PublishingYear(ByVal date As Date)
magazineYear = date
End Property
Private Property Get Document_Title() As String
Document_Title = magazineTitle
End Property
Private Property Let Document_Title(ByVal title As String)
magazineTitle = title
End Property
Private Sub Document_ShowInfo()
MsgBox "Title: " & magazineTitle & _
", Year of publication: " & magazineYear & _
", Article: " & magazineArticles
End Sub
' Code to make document properties
' available to Magazine objects
Public Property Get Title() As String
Title = Document_Title
End Property
Public Property Let Title(ByVal title As String)
Document_Title = title
End Property
Public Property Get PublishingYear() As Date
PublishingYear = Document_PublishingYear
End Property
Public Property Let PublishingYear(ByVal date As Date)
Document_PublishingYear = date
End Property
Public Sub ShowInfo()
Document_ShowInfo
End Sub
' Code for the additional properties
' (specific to the Magazine object)
Property Get Articles() As String
Articles = magazineArticles
End Property
Property Let Articles(content As String)
magazineArticles = content
End Property
All in all, the programming of derived classes is rather tedious. This mechanism is really interesting only to professional programmers who wish to create a new class library for Excel (in the form of an add-in).
Syntax Summary
KEYWORD MEMe / refers to the current instance of the object
EVENTS WITHIN THE CLASS
Class_Initialize / object of the class is created
Class_Terminate / object of the class is deleted
PROGRAMMING OF METHODS
Public Sub/Function myMethod(para) / Method without/with return value
[myMethod = …] / return value (for functions)
End Sub/Function
PROGRAMMING OF PROPERTY PROCEDURES FOR DATA (NUMBERS/CHARACTER STRINGS)
Property Get myproperty() / read property
myproperty = …
End Property
Property Let myproperty(data) / edit property
… = data
End Property
PROGRAMMING OF PROPERTY PROCEDURES FOR OBJECTS
Property Get myproperty() As Object / read property
Set myproperty = …
End Property
Property Set myproperty(obj As Object) / edit property
Set … = obj
End Property
DECLARING AND TRIGGERING EVENTS
Public Event myevent(paralist) / declaration in the class module
RaiseEvent myevent / trigger event
USE OF OBJECT CLASSES (CODE OUTSIDE OF THE CLASS MODULE)
Dim x As New classname / create object x of the class classname
x.variable / access global variables of this object
x.property / use property of this object
x.Method / use method of this object
Set x = Nothing / delete object
COLLECTION OBJECT
Dim c As New Collection c / contains a new collection
c.Count / returns the number of elements
c.Add data, "index" / add an element
c(n) or c("index") or / various syntax variants for access
c!index or c![index] / to an element
c.Remove(n) or ("index") / delete element
Set c = Nothing / delete collection