Interfaces And Implementation

This pages describes Interfaces and the Implementation of Interfaces to make your code more stremalined, faster, easier to understand, and easier to modify, debug, or enhance.

Interfaces

VBA began life as a simple scripting language that allowed you to create simple macros to automate frequently performed tasks and to write functions that you could call from worksheet cells. In the 1997 release, MS upgraded VBA to include some object oriented programming features, as well as adding the VBA Editor compenent. To be sure, VBA is not a full object-oriented programming lanauge, as it lacks important OOP features such as inheritence and function overloading. However, it does include two very important OOP features: Classes and Interfaces.

In order to use Interfaces, you need to be quite familiar with what classes and objects are in VBA and how to use them. The Classes page provides a rather in depth look at classes and objects, and how to to use them. I would strongly recommend that you read this page before continuing with this Interfaces page.

Just as a Class can be though of as a template for an Object, you might consider an Interface as a template of a Class. An interface is created in a class module, one interface per class module. You don't have to do anything special to the class module to indicate that it is to be used as an interface. On this page, we will create a very simple code model for sorting strings or peron's names. By convention, class modules that are used to define intefaces have names that begin with a captial 'I'. This is by no means required, but it keeps things orgnainzed. Also, we will use the term 'interface module' when refering to a class module that is used as an interface, and 'class module' when refering to a regular class module. This is just for clarity. Under the covers, there is no difference between a class used as an interface and a regular class, other than that the procedures defined within an interface class do not contain any executable code. An interface module contains only the procedure declarations, no code.

So, just what is an interface? An interface defines all of the properties, functions, and subs of a class, but contains no executable code. You include all of the procedure declarations along with their input parameters (if any) and the return type for functions, but no code exists within any of these procedures. An interface defines what a class exposes to the outside world, but does not contain or define how any of these procedures are carried out.

In our example of creating a program to sort arrays of strings, we will create several interfaces. There are many algorithms for sorting, each with its own strenghts and weaknesses. However, all sort algorithms always need to compare to items and determine which of the two items is greater than the other. When sorting string, that is simple; we just use the alpha bit. But if you are sorting complex objects, you would need to define what property of the object defines greater than the other object. For example, suppose you were working on a project for an automobile dealer and management wanted a sorted report of the current inventory. It would be up to you to determine what propery (e.g, VIN Number, Manufacturer, Price, etc) is to be compared in order to indicate whether one Car objet is greater than another Car object.

Since all sort algorithms need to compare two items and determine which of the two is greater, we will create an interface module that defines a function called Compare that compares two objets. Create a class module, name it IComparer, and enter the following code in the module:

    Option Explicit

    Public Function Compare(X As String, Y As String) As Long
        ' declaration only. no code allowed.
    End Function
    

Note that there is no code in the Compare function. The actual code will be in class module that implement this interace. The Compare function gets two String parameters, X and Y, and must return a Long value of -1, 0, or +1. If X is to be considered greater than Y, then Compare should return -1. If X and Y are to be considered equal, Compare should return 0. If Y is to be considered greater than X, then Compare should return +1. Returning any other value may result in unpredicable behavior.

Now, we decide that given an array of Strings consisting of people's names, there are four different ways we can sort them: 1) we can treat them as simple strings and compare them ignoring upper and lower case, 2) we can treat them as simple strings and compare them taking upper and lower case into consideration, 3) we can treat them as names and sort by last name, or, finally, 4) we can treat them as names and sort by first name. You could do all of this with one long complicated procedure, but as we shall see, using interfaces, things are very simple. We need to create four classes, one for each type of compare. Create a class module named CompareIgnoreCase and enter the following code:

    Option Explicit
    Implements IComparer

    Private Function IComparer_Compare(X As String, Y As String) As Long
        IComparer_Compare = StrComp(X, Y, vbTextCompare)
    End Function
    

The line of code Implements IComparer tell the compiler that all the methods defined in the IComparer interface module will be supported in this class. Since IComparer has only one function, Compare, we create a function called IComparer_Compare that is used to compare two items. Since this is a regular code module, the Compare function contains the logic that determines which parameter, X or Y, is greater than the other. VBA already supplies a function that does this, StrComp, so we just call that and return the result. Note that we use the vbTextCompare option to tell StrComp to ignore case, since this class is used to compare strings without regard to upper and lower case.

Next, we need a class to compare string taking upper and lower case into account. Create a new class module and name it "CompareUseCase". Put the following code in this class module:

    Option Explicit
    Implements IComparer

    Private Function IComparer_Compare(X As String, Y As String) As Long
        IComparer_Compare = StrComp(X, Y, vbBinaryCompare)
    End Function
    

As you can see, this is exactly the same as the previous class, but we use pass vbBinaryCompare rather that vbTextCompare to the StrComp function so that StrComp takes upper and lower case into account when comparing strings X and Y.

Next, we need a class to compare the text by the person's last name. We are going to make the assumption that the names are in the format of FirstName LastName. Create a class called "CompareLastName" and insert the following code:

    Private Function IComparer_Compare(X As String, Y As String) As Long
        Dim SS1() As String
        Dim SS2() As String
        SS1 = Split(X, Space(1))
        SS2 = Split(Y, Space(1))
        IComparer_Compare = StrComp(SS1(1), SS2(1), vbTextCompare)
    End Function
    

In this class, we use the Spit function to break the input parameters X and Y into arrays of strings, where the input string words are separated by a space character. The array returned by Split is always a zero-based array, so the last names will be in SS1(1) and SS2(1). We then use StrComp to compare the strings (using vbTextCompare to ignore upper and lower case) and return the result.

Finally, we need a class to compare strings by the persons' first names. Create a class named "CompareFirstName" and insert the following code.

    Option Explicit
    Implements IComparer

    Private Function IComparer_Compare(X As String, Y As String) As Long
        Dim SS1() As String
        Dim SS2() As String
        SS1 = Split(X, Space(1))
        SS2 = Split(Y, Space(1))
        IComparer_Compare = StrComp(SS1(0), SS2(0), vbTextCompare)
    End Function
    

This is exactly the same and the previous class except that we use SS(0) which contains the first names rather thna SS(1) which contains the last name. As before, we use StrComp to compare the string and return the result. At this point it is worth mentioning again that in the classes that Implement IComparer, the IComparer_Compare function can have any sort of code whatsoever. In this example, it very simple, but in the real world, the code can be as complex as is necessary, and the code in each modules that implements IComparer can have completely different logic in the IComparer_Compare function.The only restriction is that IComparer_Compare must return -1, 0, or +1. How that value is determined is completely up to you and your application's requirements.

At this point, we have four classes (plus the IComparer interface class) that allows us to choose how the array of Strings is to be supported. But we still haven't gotten to the actual sorting. Again, we will do this with an interface. Create as class named "ISortable" and enter in the code below:

    Option Explicit

    Public Function Sort(ArrayToSort() As String, Comparer As IComparer, LB As Long, UB As Long) As String()
        ' declaration only. no code allowed.
    End Function
    

This is an interface class that defines a single function named Sort which takes in the array to sort, and an object Comparer whose data type is IComparer. The actual sort procedure we use will use this Comparer object to determine how to sort the strings. Because each of our comparison class modules Implement IComparer, the Sort function can accept any of those classes, or indeed any class that Implements IComparer. Because this ISortable class is an interface class, you can Implement it in any number of classes for sorting. There are a variety of algorithms that can be used for sorting. Some are good for small numbers of items but are poor for large numbers of items. Some sort algorithms are good if the initial data is in a more or less but not exact sorted order. Depending on your application and its data, you may want to be able to specify which sort algorithm to use at run time, rather than hard-coding it at desing time. This is where the ISortable interface comes into play. This interface class defines a method called Sort, but does not have any sorting logic within it. You could have several sort classes that all implement ISortable and choose the appropriate sort class at run time. All sort algorithms have one thing in common: they repeatedluy compare two elements and determine which is the greater element. This is purpose of the IComparer interface and the classes that implement IComparer.

In this example, we are going to use only one type of sort, a modified version the standard QSort algorithm. Create a class module name CSorter and enter the following code. In the ISortable_Sort function, ArrayToSort is the unsorted array of strings to be sorted, Comparer is an instance of a class that implements IComparer and defines how the string are to be compared, and LB and UB are the bound of the array which you want to sort. Normally, you want to sort the entire array, so set both of these values to -1. It is possible, though, that you may want to sort only a subset of the array. Use LB to indicate the index of first position to sort and UB to indicate the last position to sort. However, in almost every circumstance, you will want to sort the entire arary. I don't believe I have ever encountered a situation in which I wanted to sort only a subset of the array. Nevertheless, the LB and UB parameters allow you to do this should the need arise. The result of ISortable_Sort is an array of strings sorted in the specified manner. The original input array, ArrayToSort is not modified. A sorted copy of the array is returned.

    Option Explicit
    Implements ISortable

    Private Function ISortable_Sort(ArrayToSort() As String, Comparer As IComparer, LB As Long, UB As Long) As String()
        Dim V() As String
        V = ArrayToSort
        SortValues V, Comparer, LB, UB
        ISortable_Sort = V
    End Function


    Private Sub SortValues( _
        ByRef InputArray() As String, _
        Comparer As IComparer, _
        Optional ByVal LB As Long = -1&, _
        Optional ByVal UB As Long = -1&)
    ' this procedure sorts InputArray itself, changing the positions of the elemnts in InputArray. It does not use a copy.
    Dim Temp As String
    Dim Buffer As String
    Dim CurLow As Long
    Dim CurHigh As Long
    Dim CurMidPoint As Long
    Dim Ndx As Long

    If LB < 0 Then
        LB = LBound(InputArray)
    End If
    If UB < 0 Then
        UB = UBound(InputArray)
    End If

    CurLow = LB
    CurHigh = UB

    If LB = 0 Then
        CurMidPoint = ((LB + UB) \ 2) + 1 ' note integer division (\) here
    Else
        CurMidPoint = (LB + UB) \ 2 ' note integer division (\) here
    End If
    Temp = InputArray(CurMidPoint)

    Do While (CurLow <= CurHigh)
        Do While Comparer.Compare(InputArray(CurLow), Temp) < 0
            CurLow = CurLow + 1
            If CurLow = UB Then
                Exit Do
            End If
        Loop
    
        Do While Comparer.Compare(Temp, InputArray(CurHigh)) < 0
            CurHigh = CurHigh - 1
            If CurHigh = LB Then
               Exit Do
            End If
        Loop

        If (CurLow <= CurHigh) Then
            Buffer = InputArray(CurLow)
            InputArray(CurLow) = InputArray(CurHigh)
            InputArray(CurHigh) = Buffer
            CurLow = CurLow + 1
            CurHigh = CurHigh - 1
        End If
    Loop

    If LB < CurHigh Then
        SortValues InputArray:=InputArray, Comparer:=Comparer, LB:=LB, UB:=CurHigh
    End If

    If CurLow < UB Then
        SortValues InputArray:=InputArray, Comparer:=Comparer, LB:=CurLow, UB:=UB
    End If

    End Sub
    

This class implements the ISortable interface which defines a single method, Sort. This is the procedure in the code above named ISortable_Sort. This procedure calls a private procedure named SortValues which contains the code that actually sorts the array of strings. Note that the Comparer object, which implements IComparer, is passed to this private SortValues procedure, so we can still specify how the items are to be sorted. In your own applicaiton, you could create any number of sorting classes, each of which uses a different sorting algorithm and each of which Implments ISortable. This way, you can choose at run time which sorting algorithm to use based on your data.

Now it is time to get to the meat of the application. This example code takes the text out of cells A1:A5 and creates an array of strings. Then, using a few MsgBox calls, we ask the user how he want to sort the data. This example was written in Excel and gets its initial values from worksheet cells, but the entire concept and practice of implementing interfaces has nothing to do with Excel. It can be used in any VBA application. First, lets look at the two critical variable declarations, Dim Comparer As IComparer and Dim Sorter As ISortable. You can instantiate Comparer with any new class that Implements the IComparer interface. Earlier, we created the four classes, each of which implements IComparer, to determine how to sort the data. Because they implement IComparer, you can create the Comparer variable as an instance of any one of those classes, or any other class that implements IComparer. If you look at the logic in the MsgBox selection section, you will see that depending on the user's replies, we create a New instance of Comparer as one of the four comparison classes. For example, if the user chooses to sort by last name, we use the code Set Comparer = New CompareLastNameThenFirstName to sort by last name. This works because Comparer is declared as IComparer and CompareLastNameThenFirstName implements the IComparer interface, so VBA can hook the class and the variable together via the IComparer interface.

In this example, we have only one class for doing the actual sort, which implements ISortable. Therefore, we declare a variable as Dim Sorter As ISortable. In your application, you might have several classes, each of which uses a different sorting algorithm. As long as all of those classes implement ISortable, you can create a new instance of any of those classes and assign it the to varible Sorter.

In the MsgBox seleciton logic, we determine based on the user's responses which comparison class to use. You can see that each MsgBox statement causes a Comparer to be instantiated as a different class. But as long as they all Implement IComparer, the code will work. So, for example, if the user wants to sort by first name, the code will execute the line

    Set Comparer = New CompareFirstNameThenLastName
    

Since Comparer is declared as IComparer and the class CompareFirstNameThenLastName implements IComparer, the assignment succeeds.

Next we have the varible declaration Dim Sorter As ISortable. You can instantiate this variable to a new instance of any class that implements the ISortable interface. In this example, we have only one sorting class, but there is nothing to prevent you from having many sorting classes, and as long as they all implement ISortable, you can assign any of them to the Sorter variable.

    Sub SortSomeStrings()

        Dim StringsToSort() As String
        Dim SortedStrings() As String
        Dim Comparer As IComparer
        Dim Sorter As ISortable
        Dim RangeOfStrings As Range
        Dim R As Range
        Dim N As Long

        ' set the range where the text items are listed.
        Set RangeOfStrings = Range("A1:A5")
        ' create the array to hold the strings to be sorted.
        ReDim StringsToSort(1 To RangeOfStrings.Cells.Count)
        ' load the array of string to be sorted.
        For N = 1 To RangeOfStrings.Cells.Count
            StringsToSort(N) = RangeOfStrings.Cells(N).Text
        Next N

        ' ask the user how to sort.
        If MsgBox("Sort Text?", vbYesNo) = vbYes Then
            If MsgBox("Ignore Case?", vbYesNo) = vbYes Then
                ' sort as text, ignoring upper/lower case
                Set Comparer = New CompareIgnoreCase
            Else
                ' sort as text, using upper/lower case
                Set Comparer = New CompareUseCase
            End If
        Else
            If MsgBox("Sort By Last Name?", vbYesNo) = vbYes Then
                ' sort as names, ordering by last name
                Set Comparer = New CompareLastNameThenFirstName
            Else
                ' sort as names, ordering by first name
                Set Comparer = New CompareFirstNameThenLastName
            End If
        End If

        ' create the Sorter object
        Set Sorter = New CSorter

        ' Call the Sort method of Sorter, passing to it the Comparer that will
        ' be used to detrmine how the values will be sorted.
        SortedStrings = Sorter.Sort(StringsToSort, Comparer, -1, -1)
        ' list the result to the Immediate window.
        For N = LBound(SortedStrings) To UBound(SortedStrings)
            Debug.Print SortedStrings(N)
        Next N
End Sub

    

A quick hint: if you want to sort the array in reverse (descending) order, just change the order of the parameters passed to StrComp in your class module. Instead of

    IComparer_Compare = StrComp(X, Y, vbTextCompare)
    

Use

    IComparer_Compare = StrComp(Y, X, vbTextCompare)
    

or, another way, leave the order of the parameter the same but just multiply the result of StrComp by -1.

    IComparer_Compare = -1 * StrComp(X, Y, vbTextCompare)
    

You could build this support for reversing the order into the IComparer interface module and provide support for it in the Compare class modules. In IComparer, use

    Public Function Compare(X As String, Y As String, Ascending As Boolean) As Long
        ' declaration only. no code allowed.
    End Function
    

Then in the Compare class modules, use

    Private Function IComparer_Compare(X As String, Y As String, Ascending As Boolean) As Long
        Dim N As Integer
        If Ascending = True Then
            N = 1
        Else
            N = -1
        End If
        IComparer_Compare = N * StrComp(X, Y, vbBinaryCompare)
    End Function
    

This brings up an important point to be made. Once you have defined your interface class module, you must not change the interface. Do not add, change, or remove any procedures or properties. Doing so will break any class that implements the interface and all those classes will need to be modified. Once defined, the interface should be viewed as a contract with the outside world, because other objects depend on the form of the interface. If you modify an interface, any class or project that implements that inteface will not compile and all the classes will need to be modifed to reflect the changes in the interface. This is not good. In a large application, particularly one developed by more than one programmer, your interface may be used in places of which you are not aware. Changing the interface will break code in unexpected places. If you find the absolute need to change an interface, leave the original inteface unchanged and create a new interface with the desired modifications. This way, new code can implement the new interface, but existing code will not be broken when using the original interface.

It is possible for a class to implement more than one interface. However, when you declare the variable in code, you need to specify which interface you are going to use and only methods of that interface will be available. For example, suppose that in addition to sorting, we want to count the number of strings being sorted. Let's create an interface that will count the number of items in an array or in a Collection object. Create a class module named "ICountable" and insert the following code:

    Option Explicit

    Function Count(InputObject As Variant) As Long
        ' declaration only. no code allowed.
    End Function
    

Then modify the existing CSorter class to implement both the IComparer and ICountable interfaces.

    Option Explicit
    Implements ISortable
    Implements ICountable
    

Then, insert the single Count function of the ICountable interface:

    Private Function ICountable_Count(InputObject As Variant) As Long
        If IsObject(InputObject) = True Then
            If TypeOf InputObject Is Collection Then
                ICountable_Count = InputObject.Count
            Else
                Err.Raise 5
            End If
        Else
            If IsArray(InputObject) = True Then
                ICountable_Count = UBound(InputObject, 1) - LBound(InputObject, 1) + 1
            Else
                ICountable_Count = 1
            End If
        End If
    End Function
    

This function will accept either an array or a Collection as the InputObject parameter, and return the number of elements in the array or Collection. If InputObject is any object other than a Collection object, it will raise an error. If InputObject is not an object or an array, it will return 1. To use this in code, you will have to declare a new variable whose data type is ICountable:

    Dim Counter As ICountable
    

But since we changed the CSorter class to implement both the IComparer and ICountable interfaces, we can use the existing CSorter class variable:

    Set Counter = New CSorter
    

Note that since the Counter variable was declared as ICountable, only the methods of the ICountable interface will be available through the Counter variable, even though the CSorter class implements both interfaces. It is the data type used in the Dim statement that defines how the variable will behave. We can now count the number of strings that were sorted:

    Dim X As Long
    Dim Counter As ICountable
    Set Counter = New CSorter
    X = Counter.Count(SortedStrings)
    MsgBox "Number Of Strings: " & Format(X)
    

While it is technically allowed to implement multiple interfaces in a single class, doing so can become complicated and I would recommend that you not do it until you have a solid understanding of implementing a single interface in a class. That said, implementing mutiple interfaces in a single class can provide powerful features that would otherwise be quite complicated.

Interfaces and implementation are not commonly used in VBA. I suppose this is because they require a higher level of ability and understanding than routine VBA code. They are definitely an intermediate to advanced level technique, and many developers don't want to take the time to learn how to use them. It may seem easier to write hundreds of lines of conventional VBA than to learn how to do the same thing with a few dozen lines of code using interfaces. This is unfortunate, because when properly understood and used, interfaces can make an application cleaner, more streamlined, easier to design and maintain, and easier to enhance. Using them reduces the probability of bugs, and makes any bugs that slip through much easier to find and fix. Interfaces may seem complicated at first, but once you understand them and how to use them, the advantages quickly become clear.

Like classes in general, interfaces allow you to isolate and modularize your code, which promotes more solid and stable code, as well as promoting code reusability, all of which are important when designing and developing large and complicated applications. Interfaces are certainly under used in the VBA world. I find that it is quite rare to run across code that uses them, and even more rare to find code that uses them correctly and efficiently. But it is definitely worth the time and effort to learn how to use them. The payoff when creating applications far outweighs the time it takes to learn how to use them. They should be part of any VBA developer's arsenal of programming techniques.

It may seem like a lot of work to create one or more interfaces and the classes that implement those interfaces, and in a trivially simple task like the example described above, it might be overkill. However, if you consider a large, real-world application, the benefits of interfaces and implementations become clear. Suppose you are writing an application for an automobile dealership, and you need to create a report listing the cars in inventory. The list will need to be sorted in some way, perhaps just in the order they appear in some database, or perhaps by VIN number, or manufacturer, or price. If you tried to do the sort with these options in one big monlithic sort function, the code would very quickly become very complex and prone to bugs. And even once written and debugged, modification would be complicated and error-prone. Moreover, suppose that later the dealership wants to be able to sort the report by the color of the cars. Without interfaces, you would have to modify the complicated existing sort code, putting in logic blocks in all sorts of places within the procedure. It would not be an easy task. If you design the application using interfaces, all you would have to do is create a new class that implements the IComparer interface, write the logic for the Compare function (deciding if one color is greater than another is left as an exersize for the reader), and then change one or two lines of code in the main procedure, and you're done. All of the new code logic is isolated in the new compare class, which doesn't interact with the outside world except through the one IComparer_Compare function, so you won't run the risk of introducing errors into the main program. If you were to use a single large complicated sort procedure, adding and testing a new comparision feature could take days to complete. Using interfaces and implementing those interfaces in classes, you'll be done before lunch.

You can download the workbook file with all the example code on this page.

This page last updated: 28-Oct-2008.