Modern VBA Best Practices: Default Members

Today I learned that VB.NET does in fact support Default properties. For years I was under the impression that dismissing the Set keyword meant default members couldn’t possibly exist in .NET, and I was wrong: dismissing the Set keyword meant that parameterless default members couldn’t exist in .NET, but VB.NET can still implicitly invoke a Public Property Get Item(index) default member, just like its VB6 ancestor.

Rewind to their inception, and default members/properties have all the looks of a language feature that’s considered a nice convenient way to type code faster (in 20/20 hindsight, that was at the cost of readability). That’s why and how Debug.Print Application can compile, run, and output Microsoft Excel in the debug pane; it’s why and how an ADODB.Connection object and its ConnectionString properties can be impossible to tell apart… as a convenience; how a Range “is” its value(s), a TextBox “is” its text, or an OptionButton “is” True or False.

These are the modern-day considerations for VB.NET default properties (emphasis mine, .NET-specifics removed):

Default properties can result in a small reduction in source code-characters, but they can make your code more difficult to read. If the calling code is not familiar with your class […], when it makes a reference to the class […] name it cannot be certain whether that reference accesses the class […] itself, or a default property. This can lead to compiler errors or subtle run-time logic errors. […]
Because of these disadvantages, you should consider not defining default properties. For code readability, you should also consider always referring to all properties explicitly, even default properties.

I cannot think of a single valid reason for any of these considerations to not be applicable to modern VBA, or even VB6 code. VB.NET removed the need for a disambiguating Set keyword by making a parameterless default member throw a compiler error. For contrast consider this code, and imagine the Set keyword doesn’t exist:

Dim things(9)
things(0) = New Thing

If the Thing class defines a parameterless default member, then who can tell what’s at index 0 of the things array? A Thing object reference? A SomethingElse object reference? The String representation of a Thing instance? 42?

Default members are hopefully not side-effecting magic invisible stardust code that is by definition invoked implicitly, by code that says one thing and does another, and requires looking up the documentation or the object browser definition of a type to remember what member we’re actually invoking – and even then, it can be obscured; the Excel type library is a prime example, with a hidden _Default property being the (drumroll) default property of the Range class, for example. Lastly, an implicit default member call is not 100% equivalent to an explicit one, and that tiny little difference can go as far as instantly crashing Excel.

Sounds terrible. Why would Rubberduck have a @DefaultMember annotation then?

With Rubberduck’s annotation and inspection/quick-fix system, you can easily define default members for your class modules; simply decorate the procedure with a '@DefaultMember annotation, synchronize member attributes, and done.

It’s not because you can, that you should. If you’re like me and someone gave you a knife, you’d probably at least try not to cut yourself. If you’re writing a custom collection class and you want it to be usable with the classic things(i) syntax rather than an explicit things.Item(i) member call, Rubberduck’s job is to help you do exactly that without needing to remove/export the code file, tweak it manually in Notepad++, then re-import it back into the project – that’s why the @DefaultMember annotation exists: because for the rare cases where you do want a default member, your ducky doesn’t let you down.

Currently, Rubberduck won’t complain if you make a parameterless procedure a default member. There’s an inspection idea that’s up-for-grabs to flag them though, if you’re looking for a fun contribution to an open-source project!

“I’m not a programmer”

If you’re an accountant, a sales analyst, or in any other office position where writing VBA code helps you do your job faster, you may have uttered the words “I’m not a programmer” before, and that wouldn’t have been wrong: once the code is written, you’d tweak it every once in a while to fix a bug here or there, and then move on to do your actual job as soon as things look like they work as they should. If you’re finding that as weeks and months pass, you’re spending more and more time debugging that code, it might pay off to learn a bit more about how “actual programmers” do things, but be warned: it’s a bit of a rabbit hole – in a good way, but still, it goes as deep as you’re willing to go.

If you’re a consultant delivering advanced Excel solutions to your business clients, you may have thought, said, or written the same words, too. But you like your worksheets efficient, flexible, reliable, easy to maintain; over the years you’ve become an expert at dissecting and modeling a business problem into a fine solution that will live on and grow with the client. You are a “power user”, a professional, and it shows.

Writing code involves exactly the same identical problem-solving thought process: dissecting a problem into small steps, and modeling it into …a sequence of executable statements. When you use a SUM function in a worksheet, you assess whether the range of cells you’re adding up will need to grow over time, and you make sure it’s as simple as possible to add or insert a new row without breaking the integrity of the worksheet: you’re not just solving the problem at hand, you’re anticipating the extension points, facilitating them, making it harder to break things. You shade the background of cells intended for data entry, use borders around fields, conditional formatting, and data validation to ensure everything is obvious and remains consistent; you source data validation lists from named ranges pointing to a column in a table, so that adding new possible valid entries is easy as pie and requires no other step than… adding the new possible valid entries in the table. Next to another table that requires a particular sort order because it’s being used by dynamic named ranges that source data validation lists for co-dependent dropdowns, you might put up a very obvious formatted shape with an inner text that explains why that table needs to be sorted by this column then that column, and what happens to the associated validation dropdowns when the sort is broken.

See, you are a programmer. Worksheets are programs – even more intensely so with the amazing new features coming to Excel: dynamic arrays are changing the entire paradigm and turning the very thinking of worksheet functions into something that really isn’t very far from the mindset you’d have in functional programming.

So why is it that VBA code is so often seen as merely a sequence of executable statements then? Why is it that “it works, therefore it’s good enough” is so often where the bar is? You could have made that data validation list work off a standard range of cells in some (hidden?) column somewhere off-screen, and that would have worked too… but “well, it works” isn’t where the bar should be at, and you know it.

Depending on what the code needs to do, VBA code can become much more than just a macro once you start not just solving the problem at hand, but also anticipating the extension points, facilitating them, making it easier to maintain, and harder to break things. If writing code is part of what you do for a living, then you might as well write good code. Good code isn’t just code that works. It’s code that adheres to a number of language-agnostic principles and modern-day guidelines that even plain procedural code should follow. It’s code that Joe from accounting probably couldn’t have written by themselves, but that they could likely read and understand (at least at the higher abstraction levels), and if they know enough VBA to be dangerous, they could likely even maintain and extend it!

I’m not saying every piece of VBA code needs OOP and dependency injection and inversion of control and 20 class modules with 1 method each ought to get involved in sorting a ListObject. Just that maybe, VBA code would be a little less dreadful to the eventual IT staff inheriting it, if instead of saying “I’m not a programmer”, we cared about the quality of our code in the exact same way we care about the quality of our worksheets and dashboards, or Access databases and reports, or whatever it is that we’re doing.

About Class Modules

What is a class?

The term is known to everyone that read anything about programming in VBA. It defines objects, yes. But what else do we know about them? What don’t we know about them?

VBA being built on top of COM has a number of implications, and explains a number of language features and the mechanics around classes and objects. Let’s dive into what makes a class module, and what being written in a class module means for your code.


Metadata

In order to define a class, a class module needs to contain metadata, information that VBA will use when it needs to create an object. This metadata is thoroughly specified in MS-VBAL section 4.2.

If we add a new class module to a VBA project, name it Thing, then export the code file, it should look something like this (minus Option Explicit if you don’t have the “require variable declaration” VBE setting enabled for some reason):

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Thing"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Option Explicit

This VERSION 1.0 CLASS is the header VBA is looking for – if you export a standard module, it will not have that header. It won’t have the BEGIN...END block that follows either.

MultiUse = -1 is controlled by the Instancing property, which you can find in the Properties toolwindow:

VBA supports two values for this property (VB6 has more, depending on the project type):

  • Private (default)
  • PublicNotCreateable

By default, a VBA class is private, thus only visible / only exists in the project in which it’s defined. In an add-in project, this is useful for a private/internal API. When an add-in project needs to be referenced from other VBA projects, it needs a public API – provided by PublicNotCreateable class modules. These classes can be used in referencing projects, but you can only New them up in the project they’re defined in.

We could imagine that, if a class module’s property grid had more rows than that, the other values would all be listed between the BEGIN and END keywords. So where’s the (Name) coming from?

Attribute VB_Name = "Thing"

The Attribute token, as per specifications, must always appear at the beginning of a line. Attribute names are a bit cryptic, and they’re not all specified or well-documented. In fact, different VBA host applications may support different attributes… but all VBA implementations support VB_Name and the attributes that control instantiation:

  • VB_GlobalNameSpace (false)
  • VB_Createable (false if public)
  • VB_PredeclaredId (false by default)
  • VB_Exposed (true if public)

3 of these 4 attributes are controlled by the Instancing property. VB_PredeclaredId is normally False, but special classes such as a UserForm have it set to True; the VBE provides no tooling to manipulate this attribute, but VBA will honor its value if you modify it manually (or if Rubberduck does it for you).

Instance

The instancing property of the class module determines whether & how the class is able to be instantiated; an instance of a class is an object that is defined by that class. In VBA you can create a new object with the New keyword or the CreateObject function – but CreateObject will not work with VBA user code, because the instancing modes supported in VBA don’t allow it.

Being an object, an instance of a class can only ever exist at run-time. Objects only ever exist at run-time.

There are several ways objects can come into existence in VBA.

  • Issued to the left-hand side of a Set assignment, using a New keyword or a CreateObject function call on the right-hand side;
  • Issued to a With block from a New keyword or CreateObject function call;
  • On the first encounter (or first since last destruction) of the name of a class module with the VB_PredeclaredId attribute value set to True;
  • On the first encounter (or first since last destruction) of an object reference declared with As New;

Similar to how an Excel project’s ThisWorkbook module “inherits” the Workbook members, VBA classes “inherit” a standard “VB user class” that provides two hooks on two particularly important stages in the object’s lifetime: Initialize, and Terminate.

Private Sub Class_Initialize()
End Sub

Private Sub Class_Terminate()
End Sub

Given this code:

Private Sub Test()
    Dim foo As Class1
    Set foo = New Class1

    With New Class1
    End With
End Sub

The Initialize handler would run twice – once at New Class1 as the right-hand-side of the Set assignment is being evaluated, so before the foo reference is assigned. Then once more at New Class1 in the With block, as the block is entered but before the object reference is captured by the With block.

The Terminate handler would first run at End With for the With instance, and the foo instance would terminate at End Sub, because that’s where it’s going out of scope (and it’s not Set to Nothing before that, and nothing else is keeping alive a reference to the object).

Keep in mind that declarations aren’t executable statements, so this code runs both handlers exactly 0 times:

Private Sub Test()
    Dim foo As New Class1
End Sub

The As New object is only actually created where foo is first referenced. If Class1 exposed a Public Sub DoSomething() method, and the above procedure had foo.DoSomething under the declaration, Initialize would run at the dereferencing operator (.), just before the member call to the DoSomething method; the Terminate handler would run at End Sub.

Auto-instantiated objects (declared with As New), like the default instance of class modules with a PredeclaredId attribute set to True, are re-created whenever they are no longer in scope but suddenly need to exist again:

Private Sub Test()
    Dim foo As New Class1
    foo.DoSomething
    Set foo = Nothing
    Debug.Print foo Is Nothing
End Sub

Without As New, the foo.DoSomething member call would be made against Nothing and this code would raise run-time error 91. Because of As New, the assignment to Nothing does terminate the object instance, but a new one is spawned again when foo is dereferenced again – so even though the object was just destroyed, the Debug.Print statement correctly (but confusingly) outputs False, since merely referencing the object foo has caused VBA to re-create it behind your back.

This behavior is marginally useful, but definitely not as obvious at it should be: that is why Rubberduck warns about As New declarations.


Members

The members of a class are the fields (module variables), properties, and methods of the class – whether private or public. Fields should be Private and encapsulate the instance state. Property Get members can expose private fields in a read-only manner, and Function and Sub procedures are dubbed methods.

Avoid write-only properties; if external code can write to a property, it should be able to read that value back.

A class module can define a default member by specifying a VB_UserMemId member attribute with a value of 0 (or with Rubberduck, by simply adding a '@DefaultMember annotation/comment). For collection classes, convention is to have an Item property as the default member, such that things(i) is equivalent to things.Item(i). The hidden attribute, like all attributes, is only visible when the code file is exported to be viewed in a text editor that isn’t the VBE:

'@DefaultMember
Public Property Get Item(ByVal index As Variant) As Variant
Attribute Item.VB_UserMemId = 0
    '...
End Property

In any other case, default members should be avoided: a class with a default member can be let-coerced into a value, and this easily makes things confusing – especially when considering that a default member might be returning an object that itself has a default member, that returns an object, that itself has a default member, that returns an object, …the compiler needs to work some recursive logic just to resolve what types/classes are involved: needless to say, us mere puny humans reading the code, understandably have no idea.

Let-coercion happens when you try to evaluate an object as a value expression, for example:

Debug.Print Application

We know that this instruction prints “Microsoft Excel” just like it would if we fully spelled out Debug.Print Application.Name, but we can’t know how. We can’t, because if we inspect the Application class in the Object Browser, we find that its default property is a hidden _Default get-only String property, and we can only best-guess that the implementation of that property is yielding the Name property. The result is an opaque API that does things implicitly, and nobody knows what these things are. This is bad, because at face value, Debug.Print Application makes no sense as a statement if we know that Application is an object.

If we try to let-coerce an object that doesn’t have a default member, we get run-time error 438 “object doesn’t support this property or method”, which we normally see when a late-bound member call fails. An with a hidden default member, we ensure that the inner workings of our APIs remain an arcane black box for everyone consuming our APIs. Don’t do this to your future self.

Without this language feature, the Set keyword wouldn’t be needed – assigning an object reference would never be ambiguous!

Dim rng As Variant
rng = Sheet1.Range("A1:A10") 'ambiguous: is rng the Range or its default value?
Set rng = Sheet1.Range("A1:A10") 'unambiguous: definitely the Range reference

Interfaces

Every class defines a default interface – the Public members determine what’s on that interface. When a variable is declared As Class1, the members available to IntelliSense are the members of the default interface of Class1. Interfaces cannot expose fields and/or events, so if a class defines public fields, they are exposed as read/write properties; events are simply ignored.

An interface defines how an object can interact with the outside world – or rather, how the outside world can interact with an object: it’s like a boundary, an interface between the object and its client. The object encapsulates state, its interface allows the outside world to manipulate it.

Having a default interface implies that objects can have non-default interfaces, too: an object can implement as many interfaces as needed. In VBA that’s done with the Implements keyword. And because every class defines a public interface, any class can implement any other.

Say we had a Thing class, we would declare an object variable of that type like this:

Dim instance As Thing
Set instance = New Thing

This makes the compiler understand that when we access the instance object, we are seeing it through the lens of its default Thing interface, so the member we get in the drop-down list when we type the . dereferencing operator in instance., are the public members defined in the Thing class module.

If Thing implemented an ISomething interface, and we declared it like this:

Dim instance As ISomething
Set instance = New Thing

We would still be looking at a Thing, however we would now be seeing it through the lens of its ISomething interface, and the members we would get in the drop-down list when we typed instance., would be the public members defined in the ISomething class module.

The benefits of this might seem very much theoretical and far-fetched if we’re coming from purely procedural programming, but from a COM standpoint, this is very much Tuesday – meaning, declaring explicit interfaces that formally define how we intend the outside world to be able to interact with our objects, is standard practice. .NET code intended to be consumed by COM, makes a cleaner API when it defines explicit interfaces.

When a class defines a public interface, but no implementation for these members, we give that class a generic name that we traditionally prefix with a very much Hungarian I. This makes it easy to identify purely abstract classes (interfaces) in a sea of modules. With Rubberduck’s Code Explorer, classes that are implemented by other classes, as well as classes annotated with an '@Interface annotation/comment, get a special dedicated “interface” icon that can help easily identify interface modules in a code base, prefixed or not.

This prefixing tradition was carried into modern practices, and you will find this I prefix everywhere in the .NET framework, just as you will find it in a lot of (often hidden) classes in standard VBA libraries – so in order to blend in and deliver a clear consistent message to the reader/maintainer, interface class module names should be prefixed with an I:

If you dig into the hidden classes of the Excel type library, you will find hidden interfaces that expose members that should look familiar: IAppEvents, IWorkbookEvents, IChartEvents, ICommandBarEvents, IDocEvents, IOLEObjectEvents, and many others; these hidden interfaces are your biggest clue about how interfaces and events are related, and indirectly, about how events work under COM: this mechanism explains why a VBA interface cannot expose events directly. So what are events then?


Events

Easily the very first aspect of writing VBA code we are exposed to, event handler procedures are defined by a special implicit interface that we specify in VBA code using the Event keyword in a class module. When we type the space following the RaiseEvent keyword, the editor assists by listing the members of that interface:

Raising an event can be seen as a special kind of call statement that invokes a callback – a procedure that’s written in some other class module: an event handler procedure, that may or may not execute code that we know nothing about. The handler procedure runs synchronously, so the next statement to run after a RaiseEvent statement, will run after the handler procedure has returned (this is particularly useful for events that define a ByRef cancel As Boolean parameter)… unless the handler bombed:

Option Explicit
Public Event Something(ByVal foo As String, ByVal bar As Long)

Public Sub DoSomething()
    On Error Resume Next
    RaiseEvent Something("foo", 42)
    Debug.Print "done"
End Sub

This is where the notion of callback matters: the above code will never print “done” if a handler runs into an unhandled error – execution will simply stop (and VBA will pop that familiar “unhandled error” box). If the Something handler were a “normal” procedure call, “done” would get printed whether or not an error was unhandled in the procedure.

We can handle Workbook events in the ThisWorkbook document class module, because the code of ThisWorkbook already includes every member of a Workbook class, even before we write a single line of code in it:

Debug.Print TypeOf ThisWorkbook Is Excel.Workbook 'True

This type of class inheritance unfortunately isn’t possible with VBA user code, but we can also express the relationship with composition, if we visualize the ThisWorkbook module like this:

Option Explicit
Private WithEvents Workbook As Workbook

Private Sub Class_Initialize()
    Set Workbook = Me
End Sub

The net effect being that in the ThisWorkbook module we have a Workbook event provider we can pick from the left-hand side code pane drop-down, listing the available events in the right-hand side drop-down – exactly as if there was an implicit WithEvents Workbook private field in any other class module:

ThisWorkbook module handling Workbook events for itself.
Class1 module handling Workbook events for a given Workbook object reference.

We use the WithEvents keyword to declare an object variable for which we would like to handle events. The keyword is only legal in class modules (this includes document class modules and userform modules), and the As clause may only specify a class that exposes public events. Moreover, an auto-instantiated object cannot be declared WithEvents, so As New is also illegal.

Events/callbacks are particularly useful to make application components that don’t “see” each other, still be able to talk to each other and interact: the class that’s raising an event doesn’t know if there’s someone answering the call on the other side, even less who that someone is and what they do for a living. They are a tool that can help decoupling components… to an extent: the problem is that in VBA classes, events are essentially considered implementation details – they are not exposed on the class’ default interface, even though they’re Public: the only class that is allowed to access an event, is the class that defines it – no other class can raise an event, unless we expose a method specifically for that:

Public Event Something()

Public Sub OnSomething()
    RaiseEvent Something
End Sub

Public Sub DoSomething()
    OnSomething()
    '...
End Sub

The Event and WithEvents keywords provide language-level assistance with the otherwise relatively complex wiring-up of callbacks. In order to use them with interfaces, we can use an adapter pattern with formalized SomethingEvents interfaces – like how the Battleship project demonstrates with the GridViewAdapter and IGridViewEvents classes.