In order for you to understand this article, you need to know what an Object in computer programming is. Lucky for you, I just wrote about it last week.
There are two dominating programming paradigms out there.
The basic concept is that of statements organized in modules and functions, executed in order.
Typically, there is a main subroutine at the root of the program, that in turn calls other subs or functions for execution.
Data structures are defined, populated with data and accessed when needed.
VBA code written inside a Module (not a Class Module) typically follows the procedural programming paradigm.
The basic concept is that of Objects Instances derived from Object Classes communicating with each other through their exposed Properties and Methods.
Data pertaining to an Object Instance is encapsulated within the instance alongside its given Properties and Methods.
OOP typically supports programming concepts such as encapsulation, composition, inheritance and polymorphism. These are explained below.
VBA supports close-enough OOP in Class Modules.
Languages that were designed for OOP include: Java, C++.
It’s important to note that there is no clear cut between procedural and OOP languages. Most languages were enhanced with time to support some of both, even if originally designed for either. VBA is a good example of a mix, as well as Python.
Proponent of OOP will say that OOP is best reflecting the real world, thus should be better in documenting and managing our objects and services.
Structure of an OOP program is more modular and easy to grasp. Debugging should be easier in most cases, as the error is focused in a specific object.
Polymorphism with inheritance (explained below) allows for flexible use of objects and like-objects, by defining a “tree” of objects with shared properties and methods upper in the “tree”, and more specific properties and methods in the more fine-tuned objects.
The very basic concept of re-using objects while creating new instances is appealing and highly suitable for some use cases.
Having said that, there is a never-ending debate between OOP hard-core proponents and OOP opponents.
OOP proponents will advocate for writing any program with OOP and emphasize the above-mentioned benefits.
OOP opponents claim that OOP comes with a high toll in overhead due to the “objectization” of everything, downplaying the importance of algorithmic approach, over-rating the modularity of OOP and pointing to a more complex and less efficient code.
With the basic understanding of what is an object in computer programming in mind, let’s examine what typically constitutes an OOP language.
When we think of a Class (an object’s blueprint) we need to distinguish between the internal implementation of its methods and the methods exposed as its interface when its instances come to life.
An internal (or Private) function implemented within a Class is serving its internal implementation while it is hidden in the objects (or instances) derived from that class.
Consider, for instance (pun intended) the ClearComments method of the Range object in Excel. Any given Range object can see only the ClearComments method (which is a Public function), and not the functions called by the ClearComments function itself within the Class. I can imagine that this function calls a Private Function to clear a comment from a single cell that is called in a loop over all the cells of the range in question. That single-cell function is internal (Private) to the Class.
Similarly, there are Private variables used internally, that are not exposed as Properties (or Public variables). Looping through all cells of that range may require an iteration variable (“i as Long”) that is definitely not an exposed property of the Range Class.
We therefore distinguish between the Class variables and methods on one hand, and the Instance variables and methods on the other hand.
A strong encapsulation dictates clear separation between objects’ instances.
Each instance has its own set of properties’ values, coupled with its derived capabilities (methods), in effect curbing a self-contained “capsule” with data and functions.
A well encapsulated OOP language will not allow access to any of the Class methods (which should be hidden) but only through its Instance (Public) methods.
In other words, the Public properties and methods serve as the interface for using this object, while everything else is hidden from the outer world, hence encapsulated.
Object composition is when we are nesting objects. In other words, we are referencing an object within another object.
For example, a customer may have an address and a history of purchases. Address is a Class. Purchase_History is a Class. Within the Customer Class we’ll define new instances of Address and Purchase_History. When a Customer object is instantiated, an Address and Purchase_History objects for that Customer instance will also be instantiated.
In essence, we are implementing the “Has a…” relationship between objects: A Customer has a Purchase_History.
Composition is therefore the construction of complex objects by nesting objects.
Suppose you have two types of suppliers you’re working with. Those for products and others for services. Some attributes and actions are shared, but some are unique to each type.
Every supplier has an address and a bank account, however, only product suppliers have delivering warehouse and only service suppliers have annual service fees (for example).
We can see a need for some hierarchy here to efficiently manage our suppliers. In such cases, we will define a Supplier Class that handles all of the shared properties and methods, out of which two “child” Classes (or subclasses) will be defined: The Products_Supplier Class and the Service_Supplier Class.
In essence, we are implementing the “Is a…” relationship between objects: A Service_Supplier is a Supplier.
The beauty here is that those shared properties and methods need only be maintained in the “parent” Supplier Class. As a new instance of the Product_Supplier is created, it inherits all properties and methods maintained in its “parent” object (its superclass).
Taking this example one step forward, you probably also have a Customer Class, and you can imagine some attributes are the same for suppliers and customers alike. You can see how an inheritance tree of objects takes shape here:
The point to remember is that every object inherits capabilities and attributes from its “parent” object, therefore you need not implement the same methods or duplicate the same properties throughout multiple Classes.
Considering the above customers and suppliers’ hierarchy, we understand that establishing a contract with a products supplier is a different process (and requires a different method) compared with the service supplier.
A service supplier may entail a long-term annual service agreement, while a product supplier requires product warranty attributes to be maintained.
However, when we are contracting a supplier, we need the convenience and flexibility to just say: “Create a Contract”, and the hierarchy tree should dynamically sort out its way to the appropriate method in either the Product_Supplier Class or the Service_Supplier Class.
Another type of such flexibility could be within a given Class itself. Maybe I’m allowing a new customer to be created in the system with or without a binding agreement (out of the assumption that the binding agreement can be added later).
The called method responsible of creating a new customer needs to be flexible enough to accommodate both scenarios.
The Create_Customer with an agreement method signature may look like this:
Public CreateCustomer(CustomerDetailes as Customer; CustomerAgreement as Agreement)
The Create_Customer without an agreement method signature may look like this:
Public CreateCustomer(CustomerDetailes as Customer)
Our program can now flexibly pass the agreement variable or not, and the suitable method implementation will be called for.
I know the last example looks very similar to using an Optional argument passed to a function, but remember that we’re now working with objects – not with procedural functions. There are actually TWO methods implementations within the object’s Class, not a single function handling both options.
The above described flexibility of calling methods is called polymorphism (which means the ability of an object to change its shape and form).
Routing methods within the same Class is sometimes called a Static Polymorphism and the “parent”-“child” routing is called a Dynamic Polymorphism.
As you can imagine I did not take the time to write about objects and OOP on my Blog for no benefit to those developing a career as Excel consultants – we can apply the OOP paradigm in Excel VBA as well, and you can read all about it in the next part of this Blog series about OOP by clicking right here!
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.