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.

Here you go: http://www.freevbcode.com/ShowCode.asp?ID=2970 - its for VB6 but should be the same in VBA. — Jeremy Thompson, Aug 15 '13 at 02:44
This also has what you need http://www.vb-helper.com/howto_interface_inheritance.html — , Aug 15 '13 at 12:54

score 0 · Answer 1 · answered Aug 15 '13 at 02:25

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

From the VBA Editor, select Insert > Class Module
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

hmm, not quite Jaycal. SlowCar and FastCar are not instances of Car but object themselves. The thing is they would inherit whatever properties are built in Car plus what is added/overwritten. So one does not have to copy/paste 'whatever' is all sub-types of cars. — , Aug 15 '13 at 03:40
Well, I guess I didn't "understand what you're saying" :) You're speaking of some thing more like a Java interface, where a "FastCar" would automatically have all the methods and properties of a `Car`? (pardon my naivety...) — Jaycal, Aug 15 '13 at 04:16

score 0 · Answer 2 · answered Jun 21 '16 at 10:04

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.

Build Object Hierarchy in Excel-VBA

2 Answers2