Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Polymorphic Classes

The ability of a class to appear to be many different types of object is called polymorphism and is something that many of the classes in Excel's object model use. For example, we can access the different aspects of the various menu item types using their detailed interfacesCommandBarPopUp, CommandBarButton, CommandBarComboBox and so onor iterate through them all using the more generic set of properties that they expose through the CommandBarControl interface. We can make our own classes polymorphic by simply defining and implementing multiple custom interfaces, in the same way that we added the ISortableObject interface.

For example, another requirement for our fictional book-publishing application might be to generate a letter for everyone involved in the book's production. Ideally we would like to be able to put all the CAuthor, CReviewer, CEditor and CDistributor objects into a single collection, sort the collection and then loop through it to generate the letters. Adding all the objects into one collection is not a problemthe Collection object can handle mixed object types. Assuming all those classes have implemented our ISortableObject interface, sorting the collection is not an issue eitherour generic sorting routine doesn't care what type of object it's looking at, so long as it implements the interface. The problem comes when we want to generate the lettershow do we iterate through the collection of mixed object types to get the contact details? The answer, of course, is to add another custom interface to those classes, through which we can access the contact details and other properties that are common to all the objects (assuming we've extended the earlier CAuthor class to include those details). We might choose to call it the IContactDetails interface, shown in Listing 11-10, and include the name, postal address and so forth.

Listing 11-10. The IContactDetails Interface Class

'Name:          IContactDetails
'Description:   Class to define the IContactDetails interface
'Author:        Stephen Bullen
'Get/set the name
Public Property Get Name() As String
End Property
Public Property Let Name(sNew As String)
End Property
'Get/set the postal address
Public Property Get Address() As String
End Property
Public Property Let Address(sNew As String)
End Property

The extended CAuthor, CReviewer, CEditor and CDistributor classes can implement that interface, resulting in the CAuthor class looking like Listing 11-11, where the extra code to implement the IContactDetails interface has been highlighted.

Listing 11-11. The CAuthor Class Implementing the IContactDetails Interface

'Name:          CAuthor
'Description:   Class to represent a book's author
'Allow this type of object to be sortable
'by the generic routine
Implements ISortableObject
'Provide access through the IContactDetails interface
Implements IContactDetails
Dim msAuthName As String
Dim msAddress As String
'Set/get the Author name
Public Property Let AuthorName(sNew As String)
msAuthName = sNew
End Property
Public Property Get AuthorName() As String
AuthorName = msAuthName
End Property
'Set/Get the address
Public Property Let Address(sNew As String)
msAddress = sNew
End Property
Public Property Get Address() As String
Address = msAddress
End Property
'Implement the ISortableObject class
Private Property Get ISortableObject_SortKey() As Variant
ISortableObject_SortKey = AuthorName
End Property
'Implement the IContactDetails interface,
'by calling through to the default interface's properties
Private Property Let IContactDetails_Name(RHS As String)
Me.AuthorName = RHS
End Property
Private Property Get IContactDetails_Name() As String
IContactDetails_Name = Me.AuthorName
End Property
Private Property Let IContactDetails_Address(RHS As String)
Me.Address = RHS
End Property
Private Property Get IContactDetails_Address() As String
IContactDetails_Address = Me.Address
End Property

When using the interface and procedure name drop-downs to add the Property Let procedures, the VB editor always uses RHS as the variable name for the new property value (because that represents the "right-hand side" of the property assignment expression). If the code will be doing anything other than just passing the value on to another procedure, it is a very good idea to give the variable a more meaningful name, in line with the best practices on naming conventions explained in Chapter 3 Excel and VBA Development Best Practices.

After we've added the interface to all our classes, we can add the classes to a single collection, sort the collection using the ISortableObject interface and iterate through it using the IContactDetails interface, shown in Listing 11-12. The ShowDetails procedure processes the contact details for each object in the collection, again using the IContactDetails interface, and is explained later.

Listing 11-12. Sorting and Listing Mixed Classes That Implement ISortableObject and IContactDetails

Sub CombinedIterateExample()
Dim vItem As Variant
Dim colMailList As Collection
Dim clsAuthor As CAuthor
Dim clsReviewer As CReviewer
Dim clsDetails As IContactDetails
Set colMailList = New Collection
'Add the Authors to the collection
For Each vItem In Array("Stephen Bullen", "Rob Bovey", _
"John Green")
Set clsAuthor = New CAuthor
clsAuthor.AuthorName = CStr(vItem)
colMailList.Add clsAuthor
Next
'Add some Reviewers to the collection
For Each vItem In Array("Dick Kusleika", _
"Beth Melton", "Shauna Kelly", "Jon Peltier")
Set clsReviewer = New CReviewer
clsReviewer.ReviewerName = CStr(vItem)
colMailList.Add clsReviewer
Next
'Sort the Mailing list using the generic routine
BubbleSortSortableObjects colMailList
'Although colMailList is a collection of mixed object types,
'they all implement the IContactDetails interface, so we can
'process them all by using an object variable declared
'As IContactDetails
For Each clsDetails In colMailList
ShowDetails clsDetails
Next
End Sub

We can use the TypeOf function to test whether a class implements a certain interface and switch between interfaces by declaring a variable as the type of interface we want to look through, then setting it to refer to the object, as shown in Listing 11-13. Regardless of which interface we're looking through, the VB TypeName() function will always return the object's class name.

Listing 11-13. Checking an Object's Interfaces

'Show the details of any given object
Sub ShowDetails(objUnknown As Object)
'Two variables that we can use to look at the object
'through two different interfaces
Dim clsAuthor As CAuthor
Dim clsDetails As IContactDetails
'Check if this object has the full CAuthor interface
If TypeOf objUnknown Is CAuthor Then
'Yes, so look at the object through the CAuthor interface
Set clsAuthor = objUnknown
'Write a special message for the authors
Debug.Print clsAuthor.AuthorName & " wrote the book"
'Does the object implement the IContactDetails interface?
ElseIf TypeOf objUnknown Is IContactDetails Then
'Yes, so look at it through that interface
Set clsDetails = objUnknown
'And write a message for everyone that helped
Debug.Print clsDetails.Name & " helped with the book"
Else
'An object we can't use, so write the class name
Debug.Print "Unknown Object: " & TypeName(objUnknown)
End If
End Sub