VBA Inheritance: Custom Objects as Class Properties

Inheritance is a concept that permits the transfer of data from one class to another class. While common in many programming languages, it is not native to VBA. In the absence of true inheritance, effective coding can help mimic its behavior.

Overview

This is the eleventh post in the Tips and Tricks with VBA Classes series. This post focuses on advanced features associated with VBA classes. If you are not yet familiar with VBA classes, I suggest reading our introduction to classes.

The previous two posts in this series (Storing Arrays as Class Properties and Storing Objects as Class Properties) focus on storing data structures as class properties. This post continues this topic by providing an overview on how to store other custom objects (classes) as class properties. The approach discussed in this post can be built to mimic traditional class inheritance.

Example File

The example file continues our series theme of a list of countries within a region. This specific example includes the addition of a new object: country capitals.

Two user forms exist on the spreadsheet: one for country data, the second for capital data. Countries can be added to the list (collection) through the first form. Using the dropdown list in the second form (cell B13), capital cities and accompanying data can be added to specific countries.

Macros must be enabled upon opening.

Concept

It’s well known among VBA developers that VBA is not a true object oriented programming (OOP) language. You can read our post providing an overview of the four pillars of OOP, but to summarize, VBA lacks one of the pillars: inheritance. Inheritance is the ability for one class to inherit the properties and methods of another class. This is a helpful feature for objects which can be categorized or exist in a hierarchy.

Let’s use vehicles for a practical example. If an vehicle existed as a class, it would have some basic rudimentary features: 4 wheels, a transmission, and brakes. You may also want to provide additional details about the specific vehicle, such as number of seats, engine type, and power system. What if you wanted to included details about a coup, 4-door, or hybrid car? Although all three can be classified as a vehicle, it would be difficult to fit them all in a catch-all framework, given their significant differences.

This is where inheritance can help. A vehicle class will exist to store basic information about vehicles, while multiple separate classes will exist for each type of vehicle, such as coups, 4-doors, and hybrids. The main “vehicle” class can provide all properties and details to the more specific “vehicle type” classes, making all properties and features of the class family accessible through the single vehicle class.

In terms of actual OOP, the “vehicle” class will be referred to as the “base class”, as it has all the base features to support what the object actually is. The “vehicle type” classes would be referred to as the derived classes, as they augment and support the features of the base class. In some situations, the base class may be referred to as a superclass while a derived class may be referred to as a subclass. This post will use the terms base and derived.

In our specific example, we have a base class (CapitalGeneral) “inherit” data about capital cities (CountryGeneral) for display as output. But wait — I thought VBA didn’t provide inheritance?

Does VBA Permit Inheritance?

Short answer: no; long answer: like most things in VBA, there’s a way to hack it.

VBA classes do not intrinsically support inheritance. That is, there’s no simple setup to assign a base class to a derived class (in other languages, inheritance can be obtained with one line of code). What VBA does permit is the storage of another class within a class. Through this storage, we can then access the properties of the stored “derived” class and force them to transfer through properties of the base class. This does require some setup — discussed in the following sections.

Adding a Custom Object to a Class

If you’ve previously worked with collection classes, you’re already familiar with how to store a class within a class. Even though it’s referred to as a “collection class”, syntactically, it’s just another class. However, if we take a step back, we can see that the collection class actually inherits features of its child classes (multiple objects). Even if a collection class holds hundreds of objects, they can each be accessed through the single collection (base) class.

Schematic showing how data flows with object inheritance. This post will focus on components within the red dotted line.
Schematic showing how data flows with object inheritance. This post will focus on components within the red dotted line.

In the example file, we use the RegionCollection collection class to act as a collection object, which in turn can store any number of objects derived from the CountryGeneral class. This has acted as our model throughout the series, and while this does mimic inheritance in some regards, it’s not the focus of this post. The primary focus in this post begins one level down from the collection class: the CountryGeneral class. Within the CountryGeneral class, we will store a new class: CapitalGeneral. This class will store details for each country’s capital, such as its name, population, and land area. In the schematic to the right, the focus of this post is within the red dashed line.

Derived Class

The derived class effectively “feeds” data into the base class. Given that this will be the bottom of the data structure hierarchy, it can be constructed like any normal class. In the example file, the class contains three properties: capitalName, capitalPop, and capitalLandArea. We’ve also included one member subroutine: New_Capital_Notify, which returns a message whenever a user adds a new capital. All methods and properties within the class can be found in the “Full Class Module” section near the end of this post.

Base Class

The base class is where things get somewhat interesting. We’ll spend more time focused on the property procedures later in this post, but for now, let’s discuss adding a derived class through the base class’ Insert_Capital subroutine. The subroutine is as follows:

If you have previously worked with collection classes, this may look somewhat familiar to you. It almost looks identical to a procedure to add an object to a collection class, with two primary differences.

  • On line 2, the derived object identifier is declared as the data type of the derived class (in this case, CapitalGeneral) in the global declarations area. In a collection class, this would be declared as a Collection data type.
  • On line 13, instead of adding the temporary object to a collection with VBA’s Add function, it’s simply assigned to a property to store the single derived object. Note: the property which sets the object is not in the script above, but is included in the example file. It is possible to use a Set statement in line 13 and assigned p to the global mCapitalDetails object.

These steps will store the derived class within the base class as a private variable, only accessible from within the base class. Although the derived class is now contained within a base class, this is not true inheritance. True inheritance does not require that a derived object be stored within a base object. In most true OOP languages, only a pointer statement is required to access a derived object.

We must also provide the user with a means of accessing the base class’ Insert_Capital subroutine, permitting the derived class to be added. The example file accomplishes this through the Inputs_Capital subroutine within a standard module. Note: this procedure assumes that an object from the base class already exists.

This procedure does the following:

  • Tests if the main collection exists (lines 8-10) and sets it if it doesn’t.
  • Extract values to support derived object (lines 12-15).
  • Places a call to the base class’ Insert Capital subroutine (this is done through a collection) and passes all required arguments (Line 17).
  • Reprints table data with new capital (line 19) and runs procedure confirming capital has been added (line 19, more details on this below).

Setting up Get Properties to Mimic Inheritance

One of the main benefits of true inheritance is the ease of setup for complex data structures. Instead of building massive and complex classes, a modular setup can be used to share data between classes. Unfortunately, because VBA does not offer true inheritance, we don’t reap this benefit. In addition to storing the actual derived class within the base class, a separate property must be set up for accessing each data point of the derived class — many would consider this redundant. Here’s an example of how we can use the base class to access the derived class’ capital name.

There’s no set rule that a derived object must be created at the same time as a base object, so it’s important to first check if the derived object actually exists. This is done through a simple conditional (line 3). Once existence of the derived object is confirmed, a value can be returned through the property (line 4). The property is accessed directly through the stored member object. This property—within the base class—effectively acts as a bridge between the standard module (user) and derived class.

So what’s the benefit of inheritance if we cannot obtain the true efficiency offered in other languages? While it does require some setup, this may help to organize complex data structures. This is especially useful if a base class can potentially inherit two or more derived classes. If we wanted to, this example could expand to include additional derived classes for cities/towns, rivers, or any other component that could be conceptually contained within a country. To summarize: this may seem like inheritance to a developer who is using the class, but it will NOT seem like inheritance to the developer who creates the class.

What About the Let Property?

For the purposes of this example, we’re relying on fully existent and complete objects which exist outside of a base class to be “inherited” as derived class. They already have all their data, so Let properties within the base class would be redundant. In the example file, it is possible to update existing derived object simply by overwriting it with a new capital.

Regardless, in some situations, you may want to update a derived class property through the base class. The following is an example of how to do that, simply by accessing the member object and its affected property (this property does not exist in the example file). Like its Get counterpart, you would need to write a Let property for each property within the derived class you would like to provide write/update access to.

Setting up Methods to Mimic Inheritance

Similar to derived class properties, derived class methods can also be accessed through a base class. In the example file, we use a member subroutine within the derived class to return a message whenever a new capital is added.

The following member subroutine exists in the derived class.

This procedure displays a message box providing the user with the name of the capital they just added and the country it was assigned to. Notice how this procedure includes an parameter for countryName, which will add the base object’s country name in the message. Now let’s look at how to access this method through the base class.

The base class includes a member subroutine of the same name (New_Capital_Notify) which places call to the derived class’ New_Capital_Notify member subroutine. Similar to accessing properties, the member variable (object) is used to make the call. The member variable mCountryName, containing the base object’s country name, is passed as an argument through the member subroutine.

Finally, in the standard module, a call to the base class and method will fire the procedure. This snippet is included at the end of the Inputs_Capital subroutine.

A similar approach may be used for member functions.

Output

Now that we’ve added all the derived class properties as Get properties within the base class, we can access them directly through the base class. The following is a standard output procedure which prints all country and capital data in table format.

Composition: Alternative Method of Accessing Derived Object

What do you do if a derived object is massive? Maybe it contains a few dozen properties and methods. Adding these to the base class—as done in the instructions above—will be tedious and time-consuming. It is possible to reach a derived class’ properties directly in the derived class itself. This can be done by accessing the entire CapitalGeneral class through a Get property within the base class.

This approach mimics another OOP concept known as composition. Instead of a derived class which inherits all the traits of its base class, subclass properties are accessible through a base class. This approach is more useful for classes which are not completely dependent on other classes.

The following is a sample of how this could work. Note: this is not included in the example file.

First, a Get property must be set up in the base class to provide access to the derived class within a standard module.

One important thing to remember is that to return an object through a property, the Set statement must be used (line 4). Likewise, we must test to make sure that an object of its type actually exists (line 3), and if it does not exist, we must return Nothing (line 5).

Moving to the standard module, a few simple modifications permit us to access the derived object through the base object. Note: this procedure is also not included in the example file.

We also test if the object exists in the standard module procedure (line 12). When attempting to access a property for an object that doesn’t exist, a debug error will fire. Other than the conditional, the only real difference in this example is that the object property capitalDetails (which points to the actual CapitalGeneral object) exists between the base class and the property of the derived class.

The approach takes us away from mimicking traditional inheritance, but it can be implemented more quickly and provided for some flexibility.

Full Class Modules

Derived Class

Base Class

Other Notes

If you’ve followed this series up to this point, you may notice some similarities to the data structures presented in the posts Creating a Collection Class within a Collection Class and Storing Multiple Object Collections in a Collection Class. While these posts focused on data structures used to store other collections (as opposed to other individual custom objects), the concept is effectively the same. When you take away all the technicalities, collection classes are objects. There are syntactical differences to access specific elements within a collection class, but ultimately VBA requires the same procedural steps to access specific elements.

Leave a Reply