Introduction
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, cMyClass:
Option Explicit
Public Sub Initialize()
MsgBox "This class has been initialized."
End Sub
Simple enough. To use it normally, we may create a public function in a standard module called MakeMyClass() with the following code:
Option Explicit
Public Sub MakeMyClass()
Dim x As cMyClass
Set x = New cMyClass
End Sub
But, what we'd like to do is this:
Option Explicit
Public Sub MakeMyClass(strMyClassName)
Dim x As strMyClassName
Set x = New strMyClassName
End Sub
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:
Option Explicit
Public Const ccMyClass = "MyClass"
Public Function MyClass() As cMyClass
Set MyClass = New cMyClass
MyClass.Initialize
End Function
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 CreateObject() function:
Option Explicit
Public Function CreateVBAClass(ClassName As String) As Object
Set CreateVBAClass = Application.Run(ClassName)
End Function
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)
End Sub
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.
