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

Code Reuse

One of the basic tenets of good programming is to write routines that can be reused as much as possible. For example, a generic sorting routine, such as the simple bubble sort shown in Listing 11-3, can be used to sort an array of any simple data type.

Listing 11-3. A Generic Bubble Sort

'A simple, generic, slow bubble sort, to sort a 1D array
Sub Generic1DBubbleSort(ByRef vaArray As Variant)
Dim bDoAgain As Boolean
Dim vTemp As Variant
Dim iIndex As Integer
Do
'Assume we're done
bDoAgain = False
'Loop through the array, comparing the names
For iIndex = LBound(vaArray) To UBound(vaArray) - 1
'If we found some in the wrong order, ...
If vaArray(iIndex) > vaArray(iIndex + 1) Then
'... swap them ...
vTemp = vaArray(iIndex)
vaArray(iIndex) = vaArray(iIndex + 1)
vaArray(iIndex + 1) = vTemp
'... and remember to loop again.
bDoAgain = True
End If
Next
Loop While bDoAgain
End Sub

Unfortunately, we can't use this routine to sort objects, because there is nothing in the code to say which property to sort on; every type of object would need a specific version of the routine. Let's assume that we're writing an application for a publishing company to manage the production of a book, we're using an object-oriented design and we have a CAuthor class and a CReviewer class (among others). The CAuthor class might look something like Listing 11-4 (but with more properties than just the name!).

Listing 11-4. A CAuthor Class

'Name:          CAuthor
'Description:   Class to represent a book's author
Option Explicit
Dim msAuthName As String
Public Property Let AuthorName(sNew As String)
msAuthName = sNew
End Property
Public Property Get AuthorName() As String
AuthorName = msAuthName
End Property

At some point in the application, we have the requirement to produce a list of Authors, sorted by the author's name. Because this is a collection of objects we're sorting, we cannot just pass them to a generic routine; we have to use a specific routine for each object type such as that shown in Listing 11-5 to sort a collection of Authors using the AuthorName property.

Listing 11-5. A Bubble Sort for the CAuthor Class

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

Similarly, we might need specific routines to sort collections of CReviewer, CEditor, CDistributor and so forth objects. Wouldn't it be much better if we could have a single routine that could sort collections of any of those objects? If we use a custom interface, we can!