Click here to Skip to main content
15,357,464 members
Articles / VBA
Posted 2 Mar 2011

Tagged as


11 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.80/5 (5 votes)
2 Mar 2011Ms-PL3 min read
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

GeneralMy vote of 5 Pin
MikeGTi15-Mar-21 1:51
MemberMikeGTi15-Mar-21 1:51 
It's help Me to get universal combined class with three previously unknown object items.

Option Explicit
Option Base 1
'Class for test
Public itm1 As Object
Public itm2 As Object
Public itm3 As Object

Private Sub Class_Initialize()
End Sub

Public Sub Init(ByVal itm1TypeName As String, _
                ByVal itm2TypeName As String, _
       Optional ByVal itm3TypeName As String = "")
    Set itm1 = CreateVBAClass(itm1TypeName)
    Set itm2 = CreateVBAClass(itm2TypeName)
    If Len(itm3TypeName) > 0 Then Set itm3 = CreateVBAClass(itm3TypeName)
End Sub

Private Sub Class_Terminate()
    Set itm1 = Nothing
    Set itm2 = Nothing
    Set itm3 = Nothing
End Sub

QuestionIf I understand correctly Pin
MikeGTi4-Jun-20 23:32
MemberMikeGTi4-Jun-20 23:32 
BugError in download file "Main.bas" Pin
Stef Heyenrath13-Oct-13 1:37
MemberStef Heyenrath13-Oct-13 1:37 
GeneralMy vote of 4 Pin
Steve Russo14-Mar-12 7:59
MemberSteve Russo14-Mar-12 7:59 
GeneralInteresting but... Pin
vbfengshui2-Mar-11 11:27
Membervbfengshui2-Mar-11 11:27 
GeneralRe: Interesting but... Pin
Joel C Graff2-Mar-11 12:14
MemberJoel C Graff2-Mar-11 12:14 
GeneralRe: Interesting but... Pin
vbfengshui2-Mar-11 14:30
Membervbfengshui2-Mar-11 14:30 
GeneralRe: Interesting but... Pin
Joel C Graff2-Mar-11 15:28
MemberJoel C Graff2-Mar-11 15:28 
GeneralRe: Interesting but... Pin
oharab200023-Mar-11 23:51
Memberoharab200023-Mar-11 23:51 
GeneralRe: Interesting but... Pin
Joel C Graff24-Mar-11 10:30
MemberJoel C Graff24-Mar-11 10:30 

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

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