Click here to Skip to main content
Click here to Skip to main content

Reflection in VBA (a CreateObject() function for VBA classes)

, 2 Mar 2011 Ms-PL
Rate this:
Please Sign up or sign in to vote.
A method for instantiating classes using Reflection.


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 

'reflection-level object creation 
Set xlApp = CreateObject(,"Excel.Application") 

'equivalent, type specific statement 
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 
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.


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

Joel C Graff

United States United States
No Biography provided

Comments and Discussions

BugError in download file "Main.bas" PinmemberStef_H13-Oct-13 2:37 
GeneralMy vote of 4 PinmemberSteve Russo14-Mar-12 8:59 
GeneralInteresting but... Pinmembervbfengshui2-Mar-11 12:27 
GeneralRe: Interesting but... PinmemberJoel C Graff2-Mar-11 13:14 
GeneralRe: Interesting but... Pinmembervbfengshui2-Mar-11 15:30 
GeneralRe: Interesting but... PinmemberJoel C Graff2-Mar-11 16:28 
You're right, of course. It's not really an issue about porting to .NET or anything. Really, the whole reason I even went this route is because I develop Office apps in a restrictive IT environment (DLL's and 3rd party solutions are greatly frowned upon). This has made me pay a lot of attention to what VBA is really capable of. Thus I'm motivated to make VBA do everything it can without having to draw upon external sources.
So far as performance goes, again, I'll grant that .NET would be much faster, but I've yet to have any real perfomance issues with VBA. Ok, so I coded a neural network in Excel VBA once and it was anything but quick. But the Access VBA project I've developed really suffers no loss in speed for the fact that it runs entirely in an interpreted environment. Big trick was avoiding late-binding data types and judicious use of generic calling functions (like CallByName() and Application.Run()). Interfaces were the saving grace, there. I found at least an order of magnitude difference in execution speed when comparing an interface-typed class against a class referenced through a late-bound Object or Variant.
In all honesty, this is one of those topics where I would expect the general opinion to be, "neat, but who cares?". Nevertheless, as I studied this idea of reflection in VBA, I found a variety of forum threads here and there where people were trying to accomplish this very thing. Hence, my contribution.
FWIW, in my other life, I like to code shaders in C++ and OpenGL. Smile | :)
GeneralRe: Interesting but... Pinmemberoharab200024-Mar-11 0:51 
GeneralRe: Interesting but... PinmemberJoel C Graff24-Mar-11 11:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150327.1 | Last Updated 2 Mar 2011
Article Copyright 2011 by Joel C Graff
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid