(Guest blog by Banana)
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
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:
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
MultiUse = -1 'True
Attribute VB_Name = "MyClass"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Public Sub DoIt()
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
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."
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.