4.5 Programming Your Own Classes

Up to this point we have described a number of different ways in which Excel objects or objects from external libraries can be used. The mechanisms implemented for this have been relatively stable starting with Excel 5.

New since Excel 97 is the possibility of programming one's own classes. (Classes are the templates for new objects.) Excel 2000 was again enlarged in this respect: Now custom classes can also be derived ( Implements ) and equipped with custom events.

The key to custom classes are the so-called class modules, which in the VBA development environment form their own category (in addition to normal modules and the modules associated to Excel objects). Visually, class modules look just like normal modules, that is, there is nothing to see but a code window. The difference is that the procedures defined in a class module can be used only as properties and methods of the new class. (The name of the class module is simultaneously the name of the class, and thus the correct setting in the properties window is far more important than in the case of normal modules.)

Note

The programming of custom classes can be very helpful in large projects to achieve clearer (more object-oriented) code. Furthermore, it represents an opportunity to transmit to others packaged class libraries as an add-in. However, independent of the application, class programming is a rather advanced form of Excel programming. This section gives a first overview. However, it assumes that you are already familiar with the fundamentals and major concepts of object-oriented programming.

Why Class Modules?

Let us assume that you would like to extend Excel by providing a package of statistical functions: One way to do this is simply to offer a collection of functions that contain the required algorithms. This was already possible in all previous versions of Excel. However, this way of proceeding made it impossible to proceed according to the object-oriented model in a way that also includes the management of statistical data.

Thanks to class modules you can define new objects XYPoints and XYPoint . XYPoint serves for the storing of two-dimensional data points, while XYPoints manages an entire group of such objects and makes possible, thanks to various methods and properties, the determination of statistical quantities .

To the user , these two classes might look something like the following:

 ' VBA-Concepts.xls, Module "XYTest" Sub  TestXYStatistics  ()   Dim xypts As New XYPoints   xypts.Add 3, 4   xypts.Add 7, 2   xypts.Add 6, 5   MsgBox xypts.Count & " points have been saved" & _     "The mean value of the X coordinates is " & _     xypts.XMean   Set xypts = Nothing End Sub

The user generates a new object of type XYPoints and adds three data points to this object with the method Add . Then the number of stored points and their X values are determined via the properties Count and XMean .

Note	As already mentioned, for class modules there is yet another application:You can receive events of external objects. The mechanism assumes that you previously declared an object variable with the keyword `WithEvents` . An example of this can be found in the previous section.

Class Versus Object

Perhaps the most difficult point in understanding class modules is the difference between classes and objects. A class contains the rules (methods, properties) as well as variables for storing data. An object is an instance of this class. The class is, so to speak, a template for objects. Of course, a number of objects of the same class can be used, whose contents are then independent of one another (although the methods and properties use the same code).

 Dim a As New XYPoints Dim b As New XYPoints

In the above example a and b are two object variables that refer to two objects of the class XYPoints . The two objects are generated immediately on account of the New keyword. The following example is somewhat different:

 Dim a As New XYPoints Dim b As XYPoints Set b = a

Here there is only one object, but two variables that refer to it. Any change in a has the same effect on b .

Custom Methods, Properties, and Events

In defining a new class, in the development environment you execute INSERTCLASS MODULE. With F4 you now open the properties window and give the new class a name. Then you can equip this class with event procedures and methods. Before we briefly explain these steps, here is some information about what you can do with class modules.

Equipping Classes with Methods

Defining a method for an object class is rather simple: Every procedure that is declared Public is considered a method. (Procedures declared as Private can be used only within the class module, just as with normal modules.)

You will detect a difference between public procedures in a module and a method in a class module only when they are called: While in normal modules the call is implemented simply by the procedure name, with methods an object variable must be prefixed:

 Dim a As New XYPoints Debug.Print a.Count

Equipping Classes with Properties

Property procedures are a syntactical variant of normal procedures. In this case we are dealing with procedures that when called behave formally like properties. With property procedures you can define and manage quasi-properties of a module.

Caution

To forestall possible misunderstanding we state the following:With property procedures you can neither give new properties to defined Excel objects, nor change properties that already exist. The newly defined properties refer exclusively to a class module. (Theoretically, property procedures are also permitted in normal modules, but there they make no sense.) Moreover, property procedures must not be confused with event procedures, which are described in the next section.

The most significant difference between normal procedures and property procedures is that precisely two procedures of the same name must be written. One of them is introduced with Property Get and used to read a property, while the other is introduced by Property Let and used to link new data to a property.

 ' in the class module Private mydata Property Get  MyProperty  ()             ' read property   MyProperty = mydata End Property Property Let  MyProperty  (newdata)      ' change property   mydata = newdata End Property

The example above also shows how you save data in class modules, namely, via the declaration of local variables. The access to these variables should occur exclusively through properties or methods. (Globally declared variables behave similarly to properties, but they permit no security mechanism.)

If a property is going to be able to cope with objects, instead of Property Let , the related property procedure Property Set must be defined. Further, for reading the property, Property Get is used, though the code must be altered (definition of the return value with Set ).

 ' in the class module Dim mydata As ObjectXY Property Get  MyProperty  () As ObjectXY        ' read property   Set MyProperty = mydata End Property Property Set  MyProperty  (newdata As ObjectXY) ' edit property   Set mydata = newdata End Property

Equipping Classes with Events (New in Excel 2000)

Events are defined similarly to variables in the declaration part of the class with Event . Here the parameters of the event must also be given.

 ' in the class module Public Event  MyEvent  (ByVal para As Integer)

There are two restrictions in declaring the event procedure: The event procedure is not allowed to be a function (no return value). If you wish to transmit information from the event procedure back to the calling class, you can declare separate parameters with ByRef as return parameters. Furthermore, the parameter list may contain neither optional parameters nor a parameter list.

Finally, you can trigger this procedure anywhere in the code of the class with the command RaiseEvent .

 ' likewise in the class module Public Sub  MyMethod  (x, y)   If x<y Then RaiseEvent MyEvent(57)   ... End Sub

If the user of the class schedules a MyEvent event procedure in the associated code (see below), it is called by the RaiseEvent command; otherwise , nothing happens at all. (Unfortunately, the receipt of events in Excel is possible only in class modules; see the previous section.)

The Keyword Me

Within the code of a class module you can access the current object with the keyword Me . In custom class modules this is seldom necessary. On the other hand, this keyword is particularly useful in preexisting class modules ”for worksheets, for example. For example, in the event procedure Worksheet_Activate , which is called whenever the indicated worksheet is activated, you can access the Worksheet object of this sheet with Me .

Initialize and Terminate Procedures

The procedures Class_Initialize and Class_Terminate can be defined within class modules. These procedures are automatically executed when an object of the class is generated, respectively when it is later deleted. These procedures can be used only for initialization and cleaning-up tasks .

Class Hierarchies with Implements (New in Excel 2000)

Often, one would like to define an entire group of associated classes, for example, a superclass document and classes book and magazine derived from it. Unfortunately, VBA does not recognize genuine inheritance, which would make such definitions easier. Instead, VBA supports the so-called polymorphism mechanism with the keyword Implements . With it the use of such superclasses is, in fact, syntactically possible, but the resulting code is so confusing that there are few recognizable advantages for the programmer. (After all, inheritance is supposed to save time and avoid redundancy!) The application of Implements is demonstrated in the previous section with an example.

The Instancing Property (New in Excel 2000)

In the properties window, in addition to the name, a further property is listed, namely, Instancing . The default setting is Private . This means that the class can be used only within the active Excel file and not in other Excel files (even if there is a reference to it).

If you set Instancing to PublicNonCreatable , then the class will be public. As soon as a reference to the file has been established, the class becomes visible in the object browser. However, this is still not sufficient to generate an object of this class. In other words, even when you declare a class to be PublicNonCreatable and create a reference in another Excel file, the following instructions are not permissible:

 ' attempt to generate in project B an object that is ' declared in the Excel file A Dim x As New myClass                       ' not allowed Set x = New myClass                        ' not allowed Set x = CreateObject("myProject.myClass")  ' not allowed

You are probably wondering now (as did your author at the outset) how you can possibly use objects from project A in another Excel project B . The solution is simple: You declare a public function that returns the desired object.

 ' in project A, where myClass is defined Public Function  newMyClass  () As myClass   Set newMyClass = New myClass End Function

In project B the new function newMyClass can now be placed:

 ' in project B, where myClass is to be used Dim x As myClass Set x = newMyClass()

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

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 `TOOLSREFERENCES` .

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 ME
Me	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