Building a Custom Class: Part 2

Classes and objects open up a host of opportunities within Excel VBA. In addition to being a powerful feature in their own right, classes can utilize every VBA coding technique, and permit small project teams to work on large-scale VBA projects with ease. By learning and fully understanding VBA classes and objects, you will be seen as an expert.

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


This post in the Getting Started with VBA series continues the focus of classes and objects in VBA. We continue from where last week’s post left off, elaborating on advanced topics and techniques associated with classes and objects. If you are unfamiliar with classes and objects in a programming context, read the general introduction to classes and objects. I strongly recommend that you read these two posts before proceeding.

Last week, we created a simple class to collect a passenger name and format the name in the typical format presented on plane tickets (last name, slash, first name).

Today, we’ll expand on that example by adding new pieces of data (properties), and new functionality (methods). We’ll also introduce two new intrinsic event subroutines native to classes.

Example File

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


Before going into technical details, it’s important to understand how the example file interface works for its users.
The example file focuses on data management as a passenger purchases and uses a plane ticket. Imagine that you’re an airline employee entering data into a ticketing system when a passenger calls to reserve a plane ticket (I know, very 1980s). The example file is a very simplified customer relationship management (CRM) system. From a user perspective, the CRM system works in the following steps:
1. Ticket Purchase

When a customer purchases a ticket (the airline employee clicks the “Purchase” button), the following data is collected from the form: Passenger Name, Price, and Destination. Behind the scenes (within the object), a ticket ID is generated and tax will be applied to the total cost.

2. Check-In

When the passenger checks in (clicks the “Check-In” button), a random seat number is assigned and the ticket status changes to “Checked In”. No specific data input is required from the user at the step — the user only needs to click the button.

3. Passenger Boards Flight

When the passenger boards the flight (clicks the “Board Flight” button), the status of the ticket changes to “Boarded”.

4. Flight Lands, Passenger Departs

When the passenger departs the flight, the ticket record is deleted from memory.

Screen capture of the Classy Airlines CRM system, which includes a mock-up of a plane ticket.
Screen capture of the Classy Airlines CRM system, which includes a mock-up of a plane ticket.

Class Event Subroutines

Now that we’ve introduced what our program will do from a user perspective, we can go behind the scenes in the VBA module to discuss coding. This post introduces two new intrinsic (native) event subroutines that were not discussed in the previous post: Class_Initialize and Class_Terminate.

Class Initialize

The Class_Initialize subroutine will fire when a class is Set (instantiated) within a normal coding module. This subroutine can do anything, even complete tasks that have nothing to do with the object.

In the example file, the passengerDetails object is instantiated in the coding module within the first subroutine that uses it. The Purchase_Ticket subroutine is invoked when the “Purchase Ticket” button is clicked:

In line 3 (highlighted), the New keyword within the Set statement instantiates the object.

A few effective uses of Class_Initialize include assigning initial properties, completing initial calculations, or providing feedback to the user. In the example file, the Class_Initialize subroutine sets initial values, calls a method to assign a seat, calls a method to update the ticket status to “Purchased”, then resets the user form by clearing values. The following is the Class_Initialize subroutine within the class module of the example file.

The Class_Initialize subroutine is not required for a custom class to work. However, it is a good practice to include Class_Initialize to set up a class with default values or run preliminary calculations. If not included, you simply have an empty object to work with within the main coding module.

Class Terminate

Just as there’s an event subroutine to initialize an object, there is also an event subroutine to remove an object. The Class_Terminate subroutine will fire right before an object is eliminated.

In the example file, the passengerDetails object is eliminating by assigning the value “Nothing” within a Set statement in the codin gmodule. See line 8 below.

Moving to the class module, the Class_Terminate subroutine clears all data from the ticket mock-up. From the conceptual perspective, the arline’s CRM system will no longer need the data, so it removes the passenger. This action results in elimination of the passengerDetails object and will clear the contents from the ticket mock-up.

Nothing too elaborate happens here — clears the contents of all cells containing passenger data in the ticket mock-up. Other potential uses include providing feedback to the user (message box), reformatting cells, or doing something unrelated to the object. Any attempt to access object data after it has been terminated will result in an error.

Coding Techniques with Properties

Last week’s example relied on a single property within a class. This week’s example includes a total of nine properties.

Generally, properties within classes are not complex. Each property requires a member variable, a Get procedure, and a Let procedure. You may encounter some complexity when setting objects within a class, or when using arrays. Properties can also run calculations, but in most cases, it’s better to leave those to member functions and subroutines.

In the example below, a script within the Let Property for the passengerName value formats the name for the plane ticket. By including the script within the property procedure, you force the scripting rule on any user of the class.

It’s also possible to use the Me keyword within a property to refer to another property within the same class. When using this approach, it’s important to keep the order of assignment in mind. Say within the passengerName property, you want to concatenate the seatNumber property. The seatNumber property would need to be assigned before it could be concatenated to passengerName.

Coding Techniques with Member Functions

Member functions generally serve two purposes. First, they can return data related to a property within the class. In the following example, a function calculates cost with a 5% sales tax by multiplying the ticketPrice property by 1.05. The resulting value is returned from the function. Note use of the Me keyword.

Second, member functions can return values unaffiliated with any existing properties. This is useful for including common functions associated with the class. In the following example, a random number generator returns a ticket ID. Unlike the previous example, this member function makes no reference to existing class properties.

Passing Arguments

Like functions within regular coding modules, member functions within classes can accept arguments. In the following example, the parameter gateLetter accepts a string variable to create letter and random number string. The syntax for parameters will look familiar, as highlighted in line 1 below.

The following snippet from the regular coding module shows how the argument is passed. A string value of “C” is passed as an argument through the AssignGate member function. This works like a normal function — the main difference being the member function must always be associated with an object.

Coding Techniques with Member Subroutines

Member functions and subroutines generally share the same features. The only significant difference is that a function can return a value (e.g. calculated result), where a subroutine cannot.

In the example below, a subroutine updates the property ticketStatus. All procedures within the coding module use this subroutine, so an argument passed to the function is used to test a select statement.

Why didn’t I simply make this into a member function? I could have just as easily returned a value and provided more control over the class. In this case, I did not want to give more control over the class. This member subroutine serves the sole purpose of updating the ticketStatus property.

One other item to note is the use of the EnumTicketStatus enumeration within the class. EnumTicketStatus is declared as a global enum in the main coding module. As shown in this example, global data types work in class modules too.

Other Notes

  • Like normal functions and subroutines, class methods can have arrays and objects passed as arguments. The techniques are similar — read more here.
  • Also like normal functions, class functions can return arrays and objects. The techniques are also similar — read more here.
  • Non-global objects declared within subroutines or functions will fire its Class_Terminate event once its host procedure ends.

Leave a Reply