VBA Implements

VBA Implements Tutorial – Defining a Class Interface in VBA

The VBA Implements statements specifies that the current VBA Class implements a different Class or Interface. Classes and Interfaces are a must for Object Oriented Programming (OOP). If you are not aware of OOP concepts start with my VBA Class Tutorial first.

What is an Interface?

Before we start with an example I want to briefly explain what an interface is. In short in OOP an Interface defines a set of properties (variables) and methods (Subs or Functions) that should be defined in the Class that implements the interface.

Head First Object Oriented Analysis and Design
Best book to learn OOP concepts (link to Amazon)

To give you an example imagine you want to implement a family of VBA Classes, each representing a different Car (like in my VBA Class Tutorial). As we know cars basically do the same thing – they accelerate, break, turn lights on or off etc. An interface would represent all those common characteristics that the Car Class would share. By defining an Interface you could create a Collection or Array of Cars ignoring the fact they are of different classes and set or obtain their common variables or execute their common methods.

VBA Implements example

Now we will see an example of implementing the VBA Implements statement. We will declare a set of interface variables and methods. Next we will implement the interface in 2 example classes.

Be aware that in VBA the Interface is also defined in a VBA Class module. There is no Interface module

Interface definition “CarInterface”

'Class name: CarInterface
Public Name As String
Public TopSpeed As Long
Public Sub PrintInfo() 'Only declaration is sufficient

Class definition “PorscheCayenneClass” that implements CarInterface

'Class name: PorscheCayenneClass
Implements CarInterface

'Variables for purpose of CarInterface implementation, the prefix does not matter
Private i_name As String
Private i_topSpeed As String
'Implementing CarInterface variables requires implementing Let and Get properties
Private Property Let CarInterface_Name(ByVal Name As String)
    i_name = Name
End Property
Private Property Get CarInterface_Name() As String
   CarInterface_Name = i_name
End Property
Private Property Let CarInterface_TopSpeed(ByVal TopSpeed As Long)
    i_topSpeed = TopSpeed
End Property
Private Property Get CarInterface_TopSpeed() As Long
   CarInterface_TopSpeed = i_topSpeed
End Property


'Implementing CarInterface methods
Private Sub CarInterface_PrintInfo()
    Debug.Print "Car brand and model: " & CarInterface_Name
    Debug.Print "Car top speed: " & CarInterface_TopSpeed
End Sub

'Define Porsche unique values
Private Sub Class_Initialize()
    i_name = "Porsche Cayenne"
    i_topSpeed = "241"
End Sub

Creating and using the Interface:

Sub Main()
    Dim porsche As New PorscheCayenneClass
    Dim carInt As CarInterface
    'Porsche Class casted as interface
    Set carInt = porsche
    
    'Setting interface variable and calling method
    carInt.PrintInfo
End Sub

This is the output:

Car brand and model: Porsche Cayenne
Car top speed: 241

In the example above I created a CarInterface that represents 2 variables common for all cars – name and top speed. I also included a simple procedure that prints the brand & model as well as top speed of the car.

You can access the Interface variables and methods (Subs & Functions) of the implementing Class only when casting the Class as the Interface!

Example with multiple implementations

The example above does not show us the power of VBA Implements when using more than a single Class.

On top of above we will create a new Car Class called “SkodaFabiaClass”:

Implements CarInterface

'...Copy implementation of CarInterface from PorscheCayenneClass above...

'Define Skoda Fabia unique values
Private Sub Class_Initialize()
    i_name = "Skoda Fabia"
    i_topSpeed = "203"
End Sub

Now lets put these 2 cars (Porsche & Skoda) into a VBA Array and print all its information.

Sub Main()
    Dim porsche As New PorscheCayenneClass
    Dim skoda As New SkodaFabiaClass
    Dim cars(0 To 1) As CarInterface, car As Variant
    
    Set cars(0) = porsche
    Set cars(1) = skoda
    
    'Print all car info
    For Each car In cars
        car.PrintInfo
    Next car
End Sub

This is the output:

Car brand and model: Porsche Cayenne
Car top speed: 241
Car brand and model: Skoda Fabia
Car top speed: 203

Thanks to the VBA Interface we can now treat all Car classes similar which makes working with them much easier. Obviously we can now implement logic specific to the brand/model of the car and unique methods. However, all common variables & methods should be defined in the interface.

See also  Excel VBA Cheat sheet

Conclusions

Below key conclusions:

  • VBA Implements allows you to implement an Interface to a VBA Class
  • Interfaces allow you to treat multiple different Classes that implement then as if they were defined as the same Class.
  • In VBA Interfaces are also defined in a VBA Class module. There is no module called “Interface”
  • Using Interfaces is an important aspect of OOP (Object Oriented Programming)

Have fun and use this knowledge to up your game with Object Oriented Programming.

3 Comments

  1. An interesting article Tom. I looked at VBA Implements a few years ago, but I could not see why I would use this technique, what does it give that a collection class fails to do. With a collection class, adding a new car as in your article would simply require another instance of the item class, no need to add another copy of the container class. So less coding, and you can easily add a filtering option (as discussed by Rob Van Gelder and myself a few years back on Dick Kusleika’s blog Daily Dose of Excel).

    For me, your article didn’t cast any more light. Do you have a more compelling example?

    • Do you mean a VBA Collection object? VBA Implements is about building custom containers with same interfaces but with different implementations. In the Collection example I think you are referring to (People container with Person instances) it simply assumes that each instance has the same attributes. You wouldn’t use VBA Implements for this. With VBA Implements you can make sure each container has the sample attributes AND the same methods – so there is no need to create an Interface simply for objects with the same attributes but without any methods (functions/procedures).

      To summarize – use VBA Implements MAINLY when your classes need to implement different methods (functions/procedures) but need a common interface (you want to use the Interface object as parameter in functions, procedures etc.). Hope this clarifies.

  2. The comment on the final example “‘…Copy implementation of CarInterface from PorscheCayenneClass above…” was unclear and you dont show the final code. I copied all of P-Class except define Porsche unique values and when I run the result is it shows the details for the Porsche but not the Skoda. It shows the labels but no results. It seems a bit wasteful to have to duplicate code like that. Why even call the classes Porsche/Skoda. Why not have one CarClass? So if you were going to have 50 cars, you would need 50 classes? Doesnt make sense to a complete NOOB. Can you clarify?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.