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

Defining a Custom Interface

If we want to create a generic sort routine that will work with any of our classes, we need to be able to talk to each class in the same waythat is, through the same interfaceby saying to each one "I don't care what class you are, just give me something to sort you by." To achieve that, we need to give each of our classes a custom interface, through which we can ask for the item to sort with. Our custom interface will be called ISortableObject (by convention, interfaces start with a capital I) and will have a single property called SortKey. The generic object sorting routine can then use that interface to ask each object for its key, without caring what type of class it is.

As mentioned previously, whenever we create a class module, the VBA compiler also creates an interface of the same name, containing all the public properties, methods and events that we add to the class. All we need to do to define a custom interface, then, is to create a new class module that contains the properties and methods we want to use, but doesn't have any code in the routines. VBA will create the interface for us behind the scenes, which we can then add to our other classes. So we can define our ISortableObject interface by adding a new class module, giving it the name ISortableObject and a public SortKey property, as shown in Listing 11-6.

Listing 11-6. A ISortableObject Interface Class

'Name:          ISortableObject
'Description:   Class to define the ISortableObject interface
'Author:        Stephen Bullen
'Get the key to use in the generic sorting routine
Public Property Get SortKey() As Variant
End Property

That's all there is to it. Note that we've defined the SortKey property to return a Variant data type, so we can use the same generic routine for other objects that it may be more appropriate to sort by a number or date.