Introduction to Custom VBA Classes and Objects

The ability to build classes and custom objects is one of the most valuable, yet underappreciated, features in VBA. While complex and fully functional VBA programs can be created without the use of this feature, utilizing custom objects will permit a VBA programmer to significantly scale-up a project, make it run more efficiently, and eliminate repetitive code.

Note: If you are looking for advanced topics related to VBA classes, check out our Tips and Tricks with VBA Classes series.

Overview

This post begins the final topic of the Getting Started with VBA series, providing a general conceptual overview of objects in VBA.

Some of today’s most commonly used programming languages are classified as object oriented programming (OOP) languages. These languages revolve on the creation of classes which permit programs to invoke custom objects. Objects are elements within a program that have similar traits.

While VBA can support classes and objects, it is not considered a true object oriented programming language. Typical OOPs will follow four principles, known as the Four Pillars of Object Oriented Programming: Abstraction, Encapsulation, Polymorphism, and Inheritance.

This post will provide an overview of VBA objects and discuss how VBA follows and fails to follow the four pillars. Instruction on how to build and implement objects will begin in the next three posts. Next week’s post will discuss the basics of creating classes and objects, followed by a more in depth look at advanced features, then wrapping the topic up with an introduction to building collections of custom objects.

What is OOP?

OOP, or Object Oriented Programming is a paradigm based on the use of objects to store data and perform actions. The general idea is that an unlimited number of objects (within system constraints) can be created, each containing their own attributes.

To further explain the concept, let’s use airline passengers on a New York to Boston flight as an example. Each passenger has a ticket ID, ticket price, ticket status, and seat number. This type of data could be stored in a multi-dimensional array or a spreadsheet table, but these two approaches are somewhat limiting. Multi-dimensional arrays can be difficult to build and manage, while a spreadsheet is open to data integrity issues and feature limitations. By using VBA’s custom objects feature, we can create individual objects to easily store and retrieve these values.

Before diving into the specifics of custom objects, it’s worth looking at objects which already exist in VBA’s library. One of the most frequently used objects is the Range Object. Previously, we provided a general overview of the range object, as well as an exercise related to how the range object can be used in VBA to print text on a spreadsheet. In addition to the range object, pre-set objects include almost every part of the Excel program that you interact with. Nearly everything—the application window, worksheets, scrollbars, charts, buttons, shapes, etc.—are objects native to Excel.

Are classes and objects the same thing?

Any discussion of OOP relies heavily on two elements: classes and objects. To those with a vague understanding of OOP, classes and objects may seem interchangeable. They are closely related, but are two different things. A class is the coded framework providing the properties and methods (members) of an object, whereas an object is an instance of a class. An object is always an instance of a class, much in the same way a Toyota Prius is an instance of a car. Regardless if the car is a Prius or Camry, each has similar properties and functions. In other words, there will be one of each type of class, whereas there can be one or many objects.

Going back to the earlier airline passenger example, a class would consist of the data points an airline plans to collected about a passenger. The class itself is not a passenger, but it provides a framework of traits that can be assigned to an actual passenger. In a sense, the class is waiting to “copy” itself onto a new object when a passenger reserves a ticket. When that reservation occurs, the object is created and some or all of the properties of the class will be assigned a value.

If we take a moment and peak at the non-technical business process of a ticket purchase, at the point of purchase, the airline will be able to populate some data points (properties) of the passenger object. Recall that the data the object will collect is: ticket number, ticket price, ticket status, and seat number. The purchasing process allows the airline to populate ticket number, ticket price, and ticket status. The seat number will be assigned when the passenger checks in (24 hours before the flight), and the ticket status will also update after check-in, boarding, and departure. One significant advantage of an object is the ease at which it can be called at a later time for data writing, modification, or removal.

Object Members

Objects contain members, which can be classified as properties and methods.

Properties

Properties are elements within objects that store data. Data can be written, updated, and read from Let and Get properties. These properties can also validate and format data before writing to the object. Given the nature of properties, they must always be used with an assignment operator.

In the airline passenger example, any data required by the airline is a property.

Methods

Methods are actions that the object can perform. They are built as functions and subroutines within the class. Two pre-built methods exist: Class_Initialize() and Class_Terminate(). As you can probably figure out by the name, these two methods will run procedures whenever an object is created or destroyed.

Custom methods can be created to perform actions the object is required to to complete. In the airline passenger example, a method might be needed to compute tax, or assign a random seat number.

A method that is a function can be used with an assignment operator. A method that is a subroutine only needs to be called. Methods can be assigned a public or private scope. Private methods can only be accessed within the specific class module, while public methods can be accessed anywhere the object is called, by using the dot operator along with a specific instance of the object.

The Four Pillars of Object Oriented Programming

Abstraction

VBA objects do assist with abstraction. While class modules can be full of members with complex calculations, validation rules, and algorithms, users of a class can easily access these members through basic member descriptors. By hiding an object’s complex functionality within a class module, the developer creating objects with the class may never need to see the actual code.

Encapsulation

The VBA editor provides the ability to create class modules full of private or public properties and methods, effectively encapsulating the class into a single module. By doing so, only members within that class module will be permitted to affect specific objects derived from that class. Likewise, the class module can be imported into other projects for use.

By encapsulating a class, its public members will only be associated with the class, and private members will only be accessible within the class module itself.

Polymorphism

In an OOP context, polymorphism is the idea that an object can take many forms from its host class. In Excel, for instance, multiple shape objects can exist, each with their own attributes, such as size, color, and border. Some shapes may be circles, while others may be squares or rectangles. Regardless, they are formed from the same class and each have a set number of properties and methods associated with that class.

In the airline example, polymorphism permits multiple passengers to be formed from a single class. Each passenger will have his or her own attributes and exist in the system simultaneously.

Inheritance

We leave inheritance as the final pillar, as it is the pillar which VBA does not follow. While a class can be constructed to collect an almost limitless amount of information (again, within system limitations), there may be a desire to build a more structured approach. This is where the concept of inheritance comes in — instead of modifying an existing class to add properties or methods, separate sub (derived) classes can be created to work strictly with their parent (base) class. If you’re familiar with relational databases, these classes act like separate database tables. The sub-class will inherit certain details from the base class, while objects of the base class may or may not require a sub class to be initiated.

In the airline passenger example, we can consider the primary customer data as the base class. However, let’s assume some customers have a frequent flyer account. In theory, it would be great if we could create a new subclass to store all frequent flyer details, such as ID, status, and amount of accumulated miles. The subclass would not need to be instantiated for customers who do not have a frequent flyer account, savings on system resources. Likewise, the features of the class would only be available to those passengers who do have a frequent flyer account.

Unfortunately VBA does not explicitly provide inheritance as a feature. There are ways to mimic inheritance, but these generally require redundant coding. VBA does provide the ability to group objects into collections, but the collection and object have minimal influence on each other.

Overview

While classes and objects are one of the more complex features in VBA, their usefulness is unsurpassed. By fully utilizing classes and objects, you can write clearer and more concise code. After mastering classes and objects, you will likely find them to be a much more powerful alternative to multi-dimensional arrays, data collections, and basic global variables.

Leave a Reply