Build Object Hierarchy in Excel-VBA

Question

I'm losing quite some time copy-pasting identical properties and methods in various vba custom object I'm building. How do I create an custom-object hierarchy in VBA so one object and inherit properties and methods from others.

In python I would prob write something like:

Class Car(Object)

whatever

Class SlowCar(Car)

inherit whatever

Class FastCar(Car)

inherit whatever

tks in advance.

Answer 1

If i understand what you're saying, this can be done via the Class Module.

1. From the VBA Editor, select Insert > Class Module
2. Change the name of the class Module to whatever you want (Car for example) via the Properties Window (press F4 to make it appear if it does not already)

Now that you've created your class module you can define its variables and/or properties. The example below would go into your Car Class Module creates a object that holds a car name and a speed

Private carName As String
Private carSpeed As Integer

' Car Name
Public Property Get Name() As String
    Name = carName 
End Property
Public Property Let Name(result As String)
    carName = result 
End Property 

' Car Speed
Public Property Get Speed() As Integer
    Speed = carSpeed
End Property
Public Property Let Speed(result As Integer)
    carSpeed = result 
End Property

Then from your Module, you can do the following

Sub CreateMyCars()
Dim slowCar as Car
Dim fastCar as Car

    Set slowCar = New Car
    Set fastCar = New Car

    slowCar.Name = "GoKart"
    slowCar.Speed = 35

    fastCar.Name = "Ferarri"
    fastCar.Speed = 185
End Sub

Answer 2

VBA supports inheritance through the use of Interfaces, but they only "inherit" the signature of methods, not the implementation.

A way to reuse an object implementation would be through composition.

Class Car(Object)
    whatever

Class SlowCar(Car)
    Implements Car

    private mCar         as Car

    Private Sub Class_Initialize
        set mCar = new Car
    End Sub

    Private Sub Car_whatever
        Call mCar.whatever
    End Sub 

And same for FastCar.