Sometime I just want to be single, okay?

(Guest blog by Banana)

On the surface, it appears that VBA does not like it very much when you want to have a Singleton class. Whenever I write a class module, which is kind of rare given the general patterns in Access/VBA development, it's almost inevitable I want a Singleton. The only reason it's a class in first place is because I need some variables declared WithEvents. We can't do that in a standard module, so it has to be a class module. Sometime I get away with it by sticking it in form's modules which are also a class module but sometime that is not appropriate. Whatever the reasons, VBA does not really make it easy to control how you want your custom class to be instanced. All it offers you is a pithy Instancing property, choosing either "Private" or "PublicNotCreatable", neither are even relevant to the discussion and will be ignored.

Well, turns out that it is possible to get a Singleton class in VBA after all, and I don't even have to drag out the newt eyes and bat wings (those tend to get messy). But first, let's define what should constitute a true Singleton class. Two defining features of a Singleton class is that there can be only one instance (obviously) and the access is unfettered. What do we mean by access? Well, let's take a simple class, MyClass with just one method DoIt(). The standard way to call the method is to first instantiate a variable of MyClass then call the method DoIt()as below:

Dim it As MyClass

Set it = New MyClass

it.DoIt()

This is reasonable when we can have several instance of a class, but if it's a Singleton, it's now just chaff. Why do we have to instantiate a Singleton, and more importantly, isn't the whole point of having Singleton to not have to instantiate one? If you look at the typical implementation, the design is such that you never have to worry about instantiating Singleton. You just do this:

MyClass.DoIt()

But that's not legal in VBA, right?

Well, it is. No, I promise you we don't have to sink into C++ (oh, the humanity!) to do this trickery. Here's how we make the above syntax legal:

In the VBA editor, select the class module you want to be singleton, then on menu File -> Export File... Stick it somewhere easy for you to find. Open the Notepad or text editor of your choice and select the same .cls file you just exported. You'll see something like this:

VERSION 1.0 CLASS

BEGIN

MultiUse = -1 'True

END

Attribute VB_Name = "MyClass"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = False

Attribute VB_Exposed = False

Option Compare Database

Option Explicit

Public Sub DoIt()

MsgBox "Done"

End Sub

For reasons only Microsoft knows, VBA editor will not expose the attributes which is why you'd have never seen those inside VBA editor. Think of attributes as an instruction to the compiler to how handle a chunk of code. In fact, if you google around about how to specify a default member of a class, how to make a custom class enumerable, all will tell you to do just the same and add appropriate attributes in right place.

But to make our class accessible without needing to instance it, we just want to change this line from False to True:

Attribute VB_PredeclaredId = True

Save the changes, return to the VBA editor and export it back (replacing the old class module you originally started with). With this simple change, the syntax

MyClass.DoIt()

is now legal. No need to create a variable of MyClass type. But it's not a done deal because people can still make a new instance of MyClass. Wouldn't be very good Singleton class, would it be? We just need to add this to our class module:

Private Sub Class_Initialize()

If Not Me Is MyClass Then

Err.Raise vbObjectError, "MyClass", _

"MyClass is a singleton. " & _

"No multiple instances are allowed."

End If

End Sub

Here's what's going on. When we ask VBA "a Is b", we're basically saying, "does the object A point to same instance that object B is pointing to?" Therefore:

Set a = New SomeObject

Set b = a

Debug.Print a Is b 'Returns true

Set c = New SomeObject

Debug.Print a Is c 'Returns false

So when we're asking "Me Is MyClass", we are actually asking whether Me points to the same instance MyClass is pointing to. The only way the test will pass is when we reference the instance MyClass. This then means this:

MyClass.DoIt() 'will succeed

Dim it As MyClass

Set it = New MyClass 'will fail because it Is not MyClass

it.DoIt() 'unreachable due to error above

So, with two minor changes, we've created our Singleton class, freed ourselves of the tedious work of having to instantiate a variable (and worrying whether it's already instantiated), guarantee that we won't shoot our feet later on and instantiate a class we weren't supposed to instantiate.