I've often thought about how one could instantiate a class in VBA without specifically declaring its type beforehand. Of course, my conclusion was inevitable: it can't be done. VBA does not support Reflection[^], an OOP concept which has many facets, including the ability to instantiate a class without knowing its type at design time. Reflection offers great flexibility that is agonizingly absent from VBA (at least for those of us who like to use VBA for more than it is typically intended).
Using the code
Truth be told, Reflection can occur in VBA, but only with the
CreateObject() function, and that only applies to ActiveX objects.
For example, if I wanted to create an instance of an Excel Application object without having to declare the type explicitly in code (using
Set xlApp = New Excel.Application), I can do so using the following code:
Dim xlApp As Excel.Application
Set xlApp = CreateObject(,"Excel.Application")
Set xlApp = New Excel.Application
So how can we solve this problem? I've seen a range of solutions, from enumerating a series of possible classes in a
Select...Case...End Select logic block, to a hackish (but effective) idea of writing function code on-the-fly that instantiates the specific class type.
The approach I prefer falls somewhere in between. Basically, the goal is to be able to instantiate a class of any given type by providing a string representation of the type name. In other words, an equivalent
CreateObject() function that works on user-defined VBA classes.
Let's start with an example class,
Public Sub Initialize()
MsgBox "This class has been initialized."
Simple enough. To use it normally, we may create a public function in a standard module called
MakeMyClass() with the following code:
Public Sub MakeMyClass()
Dim x As cMyClass
Set x = New cMyClass
But, what we'd like to do is this:
Public Sub MakeMyClass(strMyClassName)
Dim x As strMyClassName
Set x = New strMyClassName
Which, of course, we can't do. At least not like this. Somehow, somewhere, we need to lay down type-specific code that does the actual object creation. The trick is to use a sort of Reflection VBA does support: the
Application.Run() method. This method allows us to execute a public function stored in a standard module by simply passing its name as a string - just like we want to do with class instancing. So, we could add another standard module and call it
Support_cMyClass. Then add code like this:
Public Const ccMyClass = "MyClass"
Public Function MyClass() As cMyClass
Set MyClass = New cMyClass
What we're doing is creating a sort of "external constructor" (to borrow an idea from C++). Finally, in a "main" standard module, we add our VBA class version of the ActiveX
Public Function CreateVBAClass(ClassName As String) As Object
Set CreateVBAClass = Application.Run(ClassName)
To use our function, we merely need to invoke it with the name of our class (less the "c" prefix) using either the name in a string or the public constant defined in the class' support module:
Public Sub MakeMyClass()
Dim x As Object
Set x = CreateVBAClass("MyClass")
Set x = CreateVBAClass(ccMyClass)
In either case, when the code runs, a message box will pop up, letting us know that an instance of
cMyClass has been created and initialized. Note that the use of
Public Const acts as a sort of "dynamic enumerant". That is, it provides a sort of enumeration of strings which follows a consistent naming convention. While it is not intelli-sense friendly, it does provide us the greatest flexibility and modularity, enabling a more "plug-and-play" approach to our Reflection technique rather than requiring added classes to be enumerated in a global
Enum or function with an extensive
Select...Case...End Select logic.