Building a Custom Class: Part 1

If you’ve followed the series up to this point, you have previously worked with intrinsic (built-in) objects like Range, Shape, or Worksheet. In addition to these intrinsic objects, VBA offers the ability to build custom objects through the creation of a class.

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 in the Getting Started with VBA series continues the focus of classes and objects in VBA. If you are unfamiliar with classes and objects in a programming context, it is strongly suggested that you read our first post on the topic.

Now that you have a general understanding of what a class is and how it relates to objects, we will discuss technical details on how to build a simple class and use it to instantiate an object. Given the complex nature of custom classes and objects, the topic of creating a class has been split into two posts. This post will focus on the implementation of a very basic, bare-bones example. Part 2 will expand on the example provided in part 1, but adds complexity to introduce new concepts and features.

Example File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Class Basics

Before diving into building an actual class, it’s important to understand a few basic components and concepts. Even if you consider yourself an experienced Excel/VBA user, some of these concepts will seem completely foreign if you have never previously worked with classes.

VBA classes must be wholly contained within a class module. Within the class module, associated properties and methods can be coded. Properties are essential object attributes which are accessed through the Let and Get procedures. Members exists as functions and subroutines, providing an object with responsive and interactive features.

Additionally, intrinsic event procedures can be forced when a class is initiated or terminated. These procedures are useful but not essential — they will be discussed in the next post.

The Class Module

To begin building a class, a new Class Module must be inserted. A class module can be created by selecting Insert > Class Module from the main toolbar. Detailed instructions on adding modules can be found here.

The class module will hold all properties and members associated with the class. You are familiar with creating subroutines and functions in normal coding modules. While these types of procedures can still be added in class modules, they are treated as class methods, which cannot be called through traditional means. Likewise, class modules permit the use of property procedures, which can be used to read and write data to objects derived from the class.

Class modules can be named through the properties menu (press F4).
Class modules can be named through the properties menu (press F4).

Some of our prior posts have discussed how to name coding modules and some theory behind what to name them. While naming of normal coding modules is not required and not relevant beyond organizational purposes, the name of class modules does matter. The class module name will act as the data type name of the class, which is required when declaring new objects derived from the class. Given that this name will be referenced in procedures, it’s important to give it a unique, relevant, and meaningful name. In our example, the object derived from the class will be an airline passenger, so we will name the class “Passenger”.

The class module name can be accessed through the VBA properties menu, by pressing the F4 key while the class module is active. Additional help can be found here.

Member Variables

Up to this point in the Getting Started with VBA series, we have only worked with local variables — variables which are stored locally and are not associated with an instance of an object. Classes introduce us to member variables, which are variables of the private type associated with instances of objects. Their primary purpose is to store property values (attributes) associated with an object, for as long as that object exists in memory.

Member variables are declared as global private variables in a class module’s declaration area (above all procedures) and typically use a naming convention starting with lowercase “m” to ensure no ambiguity. Member variables will only be accessible to the Get and Let property procedures within the class (will be discussed in the next section).

A simple declaration of a single member variable is as follows:

The name given to a member variable identifier should be a proper noun or adjective, given that member variables are object attributes. Traditionally, they are typed in camelCase.

Properties

Properties are procedures which permit a class to read and write values to an object’s member variables. The two types of properties are Let and Get.

Let

The Let property permits data to be assigned to an object’s member variables (they will be stored in the object). A value is passed to the property through a parameter which is subsequently assigned to the member variable. The basic syntax is as follows:

The let procedure will write a provided value (from the spreadsheet) to a class member variable.
The Let procedure will write a provided value (from the spreadsheet) to a class member variable.

When a value is assigned to a class object from the main subroutine, a value is passed through the Value parameter (line 1). On line 3, the passed value is assigned to the private member variable mPassengerName. This member variable will store the value for as long as the object exists.

To avoid confusion between the Let and Get property, always think: “Let it be something”.

Get

The Get property gathers data from an object’s member variable (reads from the object). With a syntax that is similar to a function, the value of the member variable can be extracted by assigning it to the property name (in this case, passengerName). The Get property’s basic syntax is as follows:

The Get procedure will read a member value for use outside of a class.
The Get procedure will read a member value for use outside of a class.

To avoid confusion between the Let and Get property, always think: “I need to get something”.

Methods

Methods are procedures that perform an action associated with a class. Some common actions performed by objects may be running calculations, formatting output, or printing to a specific location. Methods can exist in two forms: a member function or a member subroutine.

One thing you will see frequently within member functions and subroutines is the Me keyword. The Me keyword will always be followed by a dot operator, then by a property (or in some cases, another method) which exists in that same class module. This permits class members to access other members of the same instance of the class. In examples later in this post, the Me keyword is used to access the passengerName property.

Member Function

A member function can be used to return a value related to an object. An example of basic syntax is as follows:

A member function is used to run an operation within a class, usually with the end result being a returned value.
A member function is used to run an operation within a class, usually with the end result being a returned value. Properties may be accessed for calculations or data manipulation.

As shown in this procedure, a data type of String is assigned to the member function (line 1). This member function’s purpose is to reverse a passenger’s first and last name and add a slash between the two (similar to how names are presented on an actual plane ticket). This is done by using the intrinsic Split function (line 5) with a space character as a delimiter. In line 6, a concatenated string is built which switches the first and late name and adds a slash in between. This concatenated string is assigned to the function name, permitting the value to be returned to its host module.

Member Subroutine

A member subroutine can be used to perform an action without returning a value (similar to a normal subroutine). The following is an example that effectively does the same operation as the previous member function:

A member subroutine is used to perform actions within a class. Existing properties may be used as part of the procedure.

The primary difference with the subroutine iteration is that the value is assigned to a specific cell range—in this case, B7—as opposed to being returned from the function (line 5).

A Complete Class

With all components of the class complete, the class module should look as follows:

Creating a Class Object and Accessing its Members

Now that a custom class has been successfully built, we can initiate an object from the class and begin accessing its members. We now move to a traditional coding module, where all the code required to initiate and work with the object will be located. From this point forward, we can treat the Passenger class as a normal object—as if it were a range or shape object—where we can access all properties and methods within the object’s class module. In fact, if you open the Object Browser (press F2), the Passenger class will be listed in bold, and all class members will be seen in the right pane when clicked.

Custom classes can be found in the object library (press F2) and will show a list of available properties and methods.
Custom classes can be found in the object library (press F2) and will show a list of available properties and methods.

Instantiating a Class

The process of creating a new object derived from a custom class is commonly referred to as “instantiating” a class. When you create an object, you are effectively creating a copy of a class.

Instantiating an object will always be the first step in utilizing a class. There are two required tasks to instantiate.

1. An object must be declared as its data type.

If you recall, one of the first steps in setting up a class was naming the class module. This name acts as the object data type name upon declaration. While not a requirement, objects are often most useful when they’re declared as global variables. This permits the object to be accessed through multiple procedures, and it will remain in memory even when procedures are not being run. Given this, we will declare a global public object named passengerDetails as a Passenger data type.

2. An object must be Set prior to accessing properties and methods.

The second task does the actual instantiating. By setting an object—usually near the top of the first procedure that will utilize it—the object is instantiated and written to memory.

In the example file, the object is Set in the first procedure to use it — a subroutine named Write_To_Class. Line 3 is where the object is Set and subsequently instantiated.

The object now exists in system memory and can be accessed through properties and members.

Writing or Updating an Object’s Properties

Similar to how you would access the value or width property of the Range object, passengerDetails object properties can be accessed using the dot operator. Assigning properties is commonly the first step after an object is instantiated. The 5th line of the Write_To_Class subroutine (immediately after it is set/instantiated) is where the passengerName property is assigned as the value within Range B2.

Typing the dot operator after an object name causes intellisense to provide a list of available members.
Typing the dot operator after an object name causes intellisense to provide a list of available members.

Assuming your VBA editor is set to its default settings, intellisense can provide help in accessing custom object members. After typing the dot operator, a dropdown menu will appear providing a list of available members. The green “flying block” represents methods, while the finger pointing on the paper represents properties.

This procedure within the coding module assigns the value in cell B2 to the passengerName property (this is done by utilizing the Range object’s Value property). Similar to assigning values to regular variables, the object and property must be to the left of the assignment operator (equals sign), while the value being assigned is to the right.

If you look at the example file spreadsheet, a simple form has been provided which asks for a passenger name in cell B2. The button labeled “Write to Object” will call the Write_To_Class subroutine, which will instantiate the object, assign the passengerName property, then store the object to system memory.

As mentioned in the previous post, one advantage of using custom class objects over arrays or basic spreadsheet data is the ease at which data stored within an object can be updated. If the passenger name property needed to be changed or corrected later in its existence, you simply need to access and write to the property just liked you did initially. The old value will be overwritten.

It’s also important to note that because the object is instantiated in this specific procedure, this procedure must be run first before trying to read any properties or access methods. Failing to do so will return a runtime error.

Reading an Object’s Properties

Reading an object’s properties is similar to writing to an object — the only difference being that the object and the element getting written two switch places. In the following example, the passengerName property of the passengerDetails object is written to cell B7.

No major surprise exists for extracting properties. Again, it’s similar to extracting property values from any intrinsic object, and intellisense will appear after typing the dot operator.

In the example file, the Read_From_Class subroutine has been assigned to the button labeled “Read from Object”.

Accessing a Member Subroutine

If you recall in the class module, we coded a member subroutine that would reverse the passenger’s first and last name, add a forward slash in between, then print the value in cell B7.

The following script within the coding module will place a call to the member subroutine. Within a call statement, include the object name, the dot operator, and the identifier of the member subroutine being called. Intellisense will make an appearance once again.

Note: the call statement actually is not required in this instance, but it’s good form to include it — adds clarity. The call statement is always required if the member subroutine requires arguments to be passed.

Accessing a Member Function

For demonstration purposes, the class contains a member function that completes an identical action to the previously accessed member subroutine. For a developer utilizing the object, the main difference between the member function vs subroutine is how it is accessed and used.

Similar to functions in normal coding modules, a member function’s primary purpose is to return a value. Member functions will not work with Call statements and should either be assigned to another variable, some other element, or used in a testing statement.

The following subroutine accesses the member function.

This approach no longer relies on a Call statement. Given that a member function returns a value, the function call itself is assigned to cell B7.

Using a Member Function vs. Member Subroutine

While the two methods presented in the example file accomplish the same task, they differ in implementation. Generally, a member function will let the object user (whether it’s you, or another developer) have more control over the use of the returned value. A member subroutine gives the user limited control, but may prevent misuse or errors. The following are advantages or disadvantages of each approach.

Member Function Advantages/Disadvantages
  • User/Developer has greater control over returned value.
  • Value returned can be assigned to any variable or to the property of another object.
  • Value can be used in a conditional statement, select statement, or loop.
  • User/Developer does not have control over any values generated in the method.
Member Subroutine Advantages/Disadvantages
  • It’s difficult for a user/developer to use the method inappropriately — will reduce debug errors.
  • Most useful for methods in which the object affects itself, e.g. calculations done on properties, etc.
  • Useful for strict actions that an object needs to complete.

Other Notes

While classes are not ideal for simple VBA tasks, they do present many advantages in larger, more complex projects. Being well versed in VBA, you can likely see how classes mimic some behaviors presented in other VBA techniques, such as arrays, collections, functions, and looping through data stored in spreadsheets.

The purpose of this post was to present a stripped down example of how to implement a simple class and instantiate an object from that class. There are some other features available, and like all things VBA, the opportunity for complexity are endless. The next post covers advanced topics in class methods and class events.

Leave a Reply