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.
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|
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|
|Home Garage||1||Moto||1200 GS||BMW||4|
|Paris Garage||2||Auto||New Bentayga||Bentley||5|
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.