VBA Subclasses

In object-oriented programming, inheritance is a mechanism that allows, when declaring a new class, to include another class characteristic. When a child class inherits from a parent class, it can then use the parent class characteristic.

To fully understand Class Modules I suggest you read this article.

VBA is an object-oriented language (OOP) that allows defining custom classes.

However, class manipulation in this language has some limitations. The notion of inheritance is not available. Thanks to manipulation and algorithm, you can attach several objects of a class to another class. It will not be strictly speaking of inheritance.

This method can be called composition.

Basic Sub-classes

Here we will illustrate the composition method through a simple garage example.

Assume that you have a garage in which you have two cars and a motorcycle. The process aims to remodel with our code the data of this garage by attaching two objects of a car class and a motorcycle object from a motocycle class.

These motorcycles and cars classes will be attached to our mother garage class.

Garage Name	Garage Code	Auto or Moto	Model	Brand	Vehicle Code
Home Garage	1	Auto	AMG Classe C	Mercedes	1
Home Garage	1	Auto	Model S	Tesla	2
Home Garage	1	Moto	GSX-R1000	Suzuki	3

We need to create a garage class with attributes and methods.

This garage class will be our parent class. Then we need to create a car daughter class and a motorcycle daughter class.

These two subclasses will be attached to the parent class through the use of the collections objects. Attributes of the parent class are not linked to the daughter class by default. Attributes and methods inheritance is not possible in VBA.

These classes will have their attributes and methods.

We will therefore start by creating the following garage class by creating a new object of class “cGarage” :

'Class cGarage

'Attributes
Public Name, Code As String

'Declare two collections 
Public AutoD As New cColObjects
Public MotoD As New cColObjects

'Initialize collections
Private Sub Class_InitializeM()

   Set MotoD = New cColObjects

End Sub

'Get property of Autos
Public Property Get Autos () As cColObjects

   Set Autos = AutoD

End Sub

'Get property of Motos
Public Property Get Motos () As cColObjects

   Set Motos = MotoD

End Sub

This garage class will have a specific attribute: an object of another class.

This other class will be an intermediate class between “cAuto” class and “cMoto” class allowing the creation of a collection of objects.

This intermediate class will be called “cColObject“. We create it and we add the following attributes and properties.

We will use our subclasses “cAuto” and “cMoto” as arguments of a method in “cColObject” to add items in the collection.

'Class cColObjects

'Attributes
Public colObjects As Collection

'Initialize collections
Private Sub Class_Initialize()

   Set colObjects = New Collection

End Sub

'Terminate collections
Private Sub Class_Terminate()

   Set colObjects = Nothing

End Sub

'Add an auto in the collection
Public Sub AddAutos (Aut as cAuto)

   colObjects.Add Aut

End Sub

'Add a moto in the collection
Public Sub AddMotos (Mot as cMoto)

   colObjects.Add Mot

End Sub

'Function to access item
Public Function Item(ByVal Index As Variant) As Variant

   Set Item = colObjects.Item(Index)

End Function

'Remove an item
Public Sub (ByVal Index As Variant)

   colObjects.Remove Index

End Sub

'Function boolean to know if an item exist in the collection
Public Function ExistsInColObject (Col As Collection, keyId As Variant) As Boolean

   For each k in Col 

      If k.Code = keyed Then
          ExistsInColObject = True
          Exit Function
     Else
          ExistsInColObject = False
     End If

   Next k

End Function

We can now create our other class “cAuto” :

'Class cAuto

'Attributes
Public Code, Model, ID, Brand As String

'Declare a collection
Public AutoD As New cColObjects

We can now create our other class Moto :

'Class cMoto

'Attributes
Public Code, Model, ID, Brand As String

'Declare a collection
Public MotoD As New cColObjects

In modules, we write a Sub procedure to create a garage class object and attach collections of “cMoto” and “cAuto” class objects. We also need to have a class with collections of objects from other classes.

Don’t forget that inheritance does not exist in VBA, but we can attach collections of class objects between class objects.

Sub CreateObjectClass()
   
    'Declarations 
     Dim Gar As Object
     Dim Mercedes_AMG As Object
     Dim Tesla_S As Object
     Dim Suzuki As Object
      
     'Create a garage
     Set Gar = New cGarage
     Gar.Name = "Home Garage"
     Gar.Code = "1"
    
     'Create a car
     Set Mercedes_AMG = New cAuto
     Mercedes_AMG.Code = "2"
     Mercedes_AMG..Model = "AMG Classe C"
     Mercedes_AMG..Brand = "Mercedes"
    
     'Create a car
     Set Tesla_S = New cAuto
     Tesla_S .Code = "2"
     Tesla_S.Model = "Model S"
     Tesla_S.Brand = "Tesla"

    'Attach autos to a garage
    Gar.Autos.AddAutos Mercedes_AMG
    Gar.Autos.AddAutos Tesla_S
     
      'Create a Moto
     Set Suzuki_GSX = New cMoto
     Suzuki_GSX .Code = "2"
     Suzuki_GSX.Model = "GSX-R1000"
     Suzuki_GSX.Brand = "Suzuki"

     'Attach moto to a garage
     Gar.Motos.AddMotos Suzuki

End Sub

After creating several class objects, we can now access our objects in the garage using the following commands:

Sub AccessGarage()

    'Create a garage and stock data auto and moto
    Call CreateObjectClass

    'Display the name of the garage (Home Garage)
    MsgBox Gar.Name 

End Sub

However, to access the attributes and method of our classes “cAuto” and “cMoto” we need to loop through the collections of objects. It is not possible to directly access an element.

Sub AccessVehicles()
'Access to all moto and auto of the garage
   
    'Create a garage and stock data auto and moto
   Call CreateObjectClass

   'Display the name of the garage (Home Garage)
   MsgBox Gar.Name 

    'Access to each element of the collection attached to the auto class
    For Each Aut In Gar.Autos.colObjects 

        'Display brand of the auto
         Msgbox Aut.Brand

    Next Aut

    'Access to each element of the collection attached to the moto class
    For Each Mot In Gar.Motos.colObjects

         'Display brand of the moto
         Msgbox Mot.Brand

    Next Mot

End Sub

The method above is simple and makes it possible to bypass issues about class connections between them.

Dictionary & Classes & Subclasses

Now, we will see how classes, dictionaries, subclasses can be effectively used in VBA.

The combination of class objects and dictionaries is explained in the following article.

Here, we will go further: we will store several garage class objects in a dictionary, which will also have objects’ collections under “cAuto” and “cMoto” classes.

This method of data storage will allow us to easily access objects by using the dictionary key and by browsing the collections.

Assume that we have several garages. Each garage has different cars and motorcycles. Our vehicles are in the following table between the three garages.

The purpose is to remodel these garages by attaching vehicles.

We will create a dictionary. The key will be a garage name and the item an object of the garage class. Each garage item will subsequently have collections of cars and motorcycles.

Garage Name	Garage Code	Auto or Moto	Model	Brand	Vehicle Code
Work Garage	3	Auto	AMG Classe C	Mercedes	1
Home Garage	1	Auto	Model S	Tesla	2
Paris Garage	2	Moto	GSX-R1000	Suzuki	3
Home Garage	1	Moto	1200 GS	BMW	4
Paris Garage	2	Auto	New Bentayga	Bentley	5
Work Garage	3	Moto	MT-15	Yamaha	6
Home Garage	1	Auto	488	Ferrari	7
Work Garage	3	Auto	Aventador	Lamborghini	8
Paris Garage	2	Auto	Ghibli	Maserati	9

Using the class modules created previously, the procedure to follow for storing data in the DicoGarage dictionary will be as follows.

We create a garage dictionary that will have several vehicles. The importance here is on testing whether the dictionary item already has the car or motorcycle in the garage object.

We will use the ExistsInColObject function that takes as arguments a collection and the code of a vehicle. This function allows us to not store the same vehicle twice in the garage.

Sub CreateGarages()
'Procedure to create garages and their vehicles attached

    'Declaration
    Dim DicoGarages As Object

    Dim Auto As cAuto
    Dim Gar As cGarage
    Dim Moto As cMoto

   'Create the dictionary
    Set DicoGarages = Nothing
    Set DicoGarages = CreateObject("Scripting.Dictionary")

    'Initialisation
    ColGarageName = 1
    ColGarageCode = 2
    ColAutoOrMoto = 3
    ColModel = 4
    ColBrand = 5
    ColVehicleCode = 6
    NbRowsVehicles = 15

    'Loop on the table in the sheet
    For i = 2 To NbRowsVehicles
        
        'Key Variable for the dictionary
        KeyGar = Cells(i,ColGarageName)

        'Create all garages stored in the dictionary
        If Not DicoGarages.Exists(KeyGar) Then
             Set Gar = New cGarage 'New garage
             Gar.Code = Cstr(Cells(i,ColGarageCode).Value2) 'Code
             Gar.Name =Cells(i, ColGarageName).Value2 'Name
             'Add garage class in the dictionary
             DicoGarages.Add Gar.Name, Gar
      End If

        'Stock Auto data in the garage
        If Cells(i, ColAutoOrMoto).Value2 = "Auto" And _
            DicoGarages(KeyGar).Autos.ExistsInColObjet(DicoGarages(KeyGar).Autos.colObjects, Cstr(Cells(i, ColVehicleCode).Value2)) = False Then
             Set Auto = New cAuto 'New Auto
             Auto.Model = Cstr(Cells(i, ColModel).Value2)  'Model
             Auto.Brand =Cells(i, ColBrand).Value2 'Brand
             Auto.Code =Cells(i, ColVehicleCode).Value2 'Code
             'Add the auto in the garage stored in the dictionary
             DicoGarages(KeyGar).Autos.AddAutos Auto
      End If

        'Stock Moto Data for the garage
        If Cells(i, ColAutoOrMoto).Value2 = "Moto" And _
            DicoGarages(KeyGar).Autos.ExistsInColObjet(DicoGarages(KeyGar).Autos.colObjects, Cstr(Cells(i, ColVehicleCode).Value2)) = False Then
             Set Moto = New cMoto 'New Moto
             Moto.Model = Cstr(Cells(i, ColModel).Value2) 'Model
             Moto.Brand =Cells(i, ColBrand).Value2 'Brand
             Moto.Code =Cells(i, ColVehicleCode).Value2 'Code
             'Add the moto in the garage stored in the dictionary
             DicoGarages(KeyGar).Motos.Add Motos Moto
      End If

 Next i

End Sub

The procedure below allows for displaying the results stored in the dictionary for each garage.

Sub DisplayGarages()
'Procedure to display data stored in garages

      Call CreateGarages 'call the procedure to stock data in the dictionary

     'Initialisation
     ColGarageName = 1
     ColGarageCode = 2
     ColAutoOrMoto = 3
     ColModel = 4
     ColBrand = 5
     ColVehicleCode = 6
      i = 0

      'Loop on each key of the dictionary
      For Each k In DicoGarages.Keys

          i = i+1 'incrementation of i for displaying data in cells
          'Display garage data
          Cells(i,ColGarageName).Value2 = DicoGarages.Item(k).Name
          Cells(i,ColGarageCode).Value2 = DicoGarages.Item(k).Code
         
          'Loop on each auto attached to the garage through the collection
          For Each Aut In DicoGarages.(k).Autos.colObjects

              i = i+1  'incrementation of i for displaying data in cells
              Cells(i, ColAutoOrMoto).Value2 = "Auto"
              Cells(i, ColModel).Value2 = Aut.Model
              Cells(i, ColBrand).Value2 = Aut.Brand
              Cells(i, ColVehicleCode).Value2 = Aut.Code

         Next Aut

         'Loop on each moto attached to the garage through the collection
         For Each Mot In DicoGarages.(k).Autos.colObjects

              i = i+1 'incrementation of i for displaying data in cells
              Cells(i, ColAutoOrMoto).Value2 = "Moto"
              Cells(i, ColModel).Value2 = Mot.Model
              Cells(i, ColBrand).Value2 = Mot.Brand
              Cells(i, ColVehicleCode).Value2 = Mot.Code

         Next Mot

      Next k

End Sub

In conclusion, as explained in the article on classes, storing class objects in a dictionary is a fast and efficient method to retrieve data.

As part of object-oriented programming, the principle of inheritance is not available in VBA.

However, we can attach collections of class objects to other classes using an intermediate collection class that has properties and methods on the object’s pre-existence.

Note that it is possible to create a class tree with some code manipulation. This bypass method is less efficient than using the powerful Python language for data manipulation but remains very efficient for modeling objects.

Basic Sub-classes

Dictionary & Classes & Subclasses

Share this: