VBA

The Secret to Achieving Naming Bliss in VBA Class Modules

This little-known technique from Rubberduck VBA maintainer and MVP Mathieu Guindon will change how you write class modules forever.

Mike Wolfe

Nov 28, 2023 • 6 min read

Naming is hard.

Within that broader topic are several sub-categories of naming challenges. Among these is the question of how to name member variables in class modules.

"Member variables" have module-level scope, meaning they are available to all procedures within the module but are not visible to code in other modules. Member variables have particular importance within class modules because they store state information about the instance of the class.

Naming these member variables is deceptively difficult.

Oftentimes, these member variables act as backing values for the class's public properties. Rather than expose the values directly as public variables, there are many side benefits to instead making them available through so-called property "getters" and "setters," such as caching, just-in-time object initialization, self-healing object variables, etc.

This leads to a dilemma, though. What do you name the private member variable to both associate it with–yet distinguish it from–its associated public property?

Naming Conventions for Member Variables

Case-sensitive programming languages often use a convention where the private member variable starts with a lower-case letter while the public property uses the exact same word but starting with an upper-case letter. Obviously, that's not an option for a case-insensitive language like VBA.

One common VBA convention is to use a lower-case "m" as a prefix for a module-level private variable. This isn't a bad option. Unfortunately, VBA's case-changing "feature" occasionally wreaks havoc on this approach. For a public property named Other, you might define a backing variable named mOther. This will be fine until you later define a variable in a different part of your program named Mother.

Suddenly, the backing variable for Other appears to be Mother.

The compiler won't care, but the humans who have to read your code will be udderly confused.

`this` is a Better Alternative

The approach I favor is one that I first read about on the Rubberduck VBA blog:

Create a custom type private to the class module, e.g. Private Type typThis
Create a member variable of this custom type, e.g. Dim this As typThis
Define all your other member variables as members of the custom type

Here's an example:

Private Type typThis
    IsInitialized As Boolean 
    FirstName As String 
    LastName As String 
    BornOn As Variant  'Date 
    Children As Collection 
End Type 
Private this As typThis

Let's see how we can incorporate "this" variable among several other class module topics I've written about in the past.

Parameterized Constructors in VBA

Let's say we want to expose IsInitialized as a read-only property of the class.

One could refer to this property to see if the required Init() method had been run already (Init() is a workaround for the fact that VBA classes do not support parameterized constructors):

Public Sub Init(EmployeeID As Long)
    'Do some stuff to initialize the class
    this.IsInitialized = True
End Sub 

Public Property Get IsInitialized() As Boolean
    IsInitialized = this.IsInitialized
End Property

Easy enough.

Simple Property "Getters" and "Setters"

Now let's expose FirstName and LastName as read-write class properties:

Public Property Get FirstName() As String
    FirstName = this.FirstName
End Property 
Public Property Let FirstName(Value As String)
    this.FirstName = Value
End Property 

Public Property Get LastName() As String
    LastName = this.LastName 
End Property 
Public Property Let LastName(Value As String)
    this.LastName = Value
End Property

Using separate backing variables–rather than just directly exposing FirstName and LastName as public, module-level string variables–probably seems like a lot of effort for little gain.

Poor Man's Nullable Type Validation

The BornOn property is a better example of why you might want to use private member variables instead of directly exposing the value as a public variable.

This type member is defined as a Variant–rather than a seemingly more appropriate type of Date–because it represents an optional database field. Since only a Variant can hold a Null value, VBA leaves us no other choice.

However, we can use the public property Let statement to perform some basic validation of the BornOn value to make sure we don't allow assigning non-dates to this value. For example:

Public Property Get BornOn() As Variant  'Date
    BornOn = this.BornOn
End Property 

Public Property Let BornOn(Value As Variant)  'Date
    If IsNull(Value) Then 
        this.BornOn = Null
    Else 
        'This line will throw an error if calling code tries to 
        '    assign a non-date value to the BornOn property 
        this.BornOn = CDate(Value)
    End If 
End Property

Cached and Self-Healing Object Variables

Finally, we can use member variables with class-level object variables to perform both just-in-time instantiation and self-healing object variables. For example:

Public Property Get Children() As Collection
    If this.Children Is Nothing Then
        Set this.Children = PopulateChildrenCollection()
    End If 
    Set Children = this.Children
End Property

Let's assume PopulateChildrenCollection() is an expensive operation. It's not something we should do more than necessary. If we exposed Children directly as a public variable, we could limit the number of times we instantiate it by doing it once in the Class_Initialize() event or in a dedicated Init() class method.

However, that has two problems:

A code reset would wipe out the contents of the Children variable
Initializing the collection for every instance of the class would be a waste for any class instances that don't then reference the collection

The sample code above addresses both limitations.

Every time the Children property is called, it first checks to see if the member variable (this.Children) has been initialized. If it has not, then the Property Get statement calls the PopulateChildrenCollection() method to, well, populate the Children collection (see how self-documenting code works?).

Just as importantly, if the this.Children object has been initialized, then the code skips over that expensive initialization step and simply returns the saved object.

Refreshing the Cache

Many of my class modules include a Refresh method, whose sole purpose is to set member object variables to Nothing. Observe:

Public Sub Refresh()
    Set this.Children = Nothing
End Sub

That's it! The next time calling code retrieves the contents of the Children collection property, the class will repopulate the this.Children object first, via the self-healing object variable pattern.

Drawbacks of the Approach

For me, the only real drawback to this approach is that it messes with my otherwise-fully-automated global vbWatchdog error handler.

Using the built-in "Variables Inspector" feature of vbWatchdog, my global error handler iterates through the values of every variable at every level of the call stack. I have custom handling for certain class modules, including my custom SQL builder, clsSQL. However, to my knowledge, there is no way to automatically access the member values of custom types such as typThis.

Fortunately for you, this drawback almost certainly does not affect you. In fact, I might be about the only VBA developer in the world for whom this is an issue.

Honestly, other than that one admittedly-niche use case, I can't think of any other good reason not to recommend this approach.