Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Using a Custom Interface

With the custom ISortableObject interface defined and implemented in our CAuthor class, we can modify our BubbleSortAuthors routine to be able to sort collections of any class that implements our ISortableObject interface, shown in Listing 11-8. All we need to do is to define our data types As ISortableObject instead of As CAuthor, use the SortKey property instead of AuthorName and change the variable names to be more generic.

Listing 11-8. A Bubble Sort for Classes That Implement ISortableObject

'A simple bubble sort, to sort a collection of objects
'that implement ISortableObject
Sub BubbleSortSortableObjects(ByRef colSortable As Collection)
Dim bDoAgain As Boolean
Dim iIndex As Integer
Dim clsSortable1 As ISortableObject
Dim clsSortable2 As ISortableObject
Do
'Assume we're done
bDoAgain = False
'Loop through the collection, comparing the names
For iIndex = 1 To colSortable.Count - 1
'Get the objects from the collection at this point
Set clsSortable1 = colSortable(iIndex)
Set clsSortable2 = colSortable(iIndex + 1)
'If we found some in the wrong order, ...
If clsSortable1.SortKey > clsSortable2.SortKey Then
'... swap them ...
colSortable.Remove iIndex + 1
colSortable.Add clsSortable2, , iIndex
'... and remember to loop again.
bDoAgain = True
End If
Next
Loop While bDoAgain
End Sub

We can then use this routine with any type of object that implements the ISortableObject interface, as shown in Listing 11-9. This technique assumes that the values provided by each object's ISortableObject_SortKey property can be used within a "greater than" comparison.

Listing 11-9. Using the Generic Sorting Routine for a Collection of CAuthors

Sub AuthorSortExample()
Dim vItem As Variant
Dim colAuthors As Collection
Dim clsAuthor As CAuthor
Set colAuthors = New Collection
'Populate the Authors collection
For Each vItem In Array("Stephen Bullen", "Rob Bovey", _
"John Green")
Set clsAuthor = New CAuthor
clsAuthor.AuthorName = CStr(vItem)
colAuthors.Add clsAuthor
Next
'Sort the Authors using the generic routine
BubbleSortSortableObjects colAuthors
'Show the sorted list
For Each clsAuthor In colAuthors
Debug.Print clsAuthor.AuthorName
Next
End Sub

That was a very quick introduction to custom interfaces, so let's recap what we've achieved and why we're doing it. When we create nontrivial object models, we often end up with multiple object types (that is, classes) that have a lot of properties in common, but some significant differences. We also often need to process many of those object types in similar ways (such as sorting them). We could do this using a variable declared As Object and hope that all our classes use the same names for their common properties, but that is neither robust nor efficient. Instead, we can define a custom interface which contains the properties and methods that are common to our objects and add code to each class to implement the interface. Our processes can then communicate with any of those object types through the custom interface, making our code much more robust, efficient, maintainable and reusable.