VBA - Create a new object using the text name of the class

Question

Is there a way to set an object to the new instance of a class by using the text name of the class?

I will have a library of classes, and depending on some other variable, I want to get one of these classes at runtime.

E.g. I have "CTest1", "CTest2", "CTest3"

I would have function similar to the below

Function GetTestClass(lngClassNo as long) as Object

Dim strClassName as String
strClassName = "CTest" & CStr(lngClassNo)

Set GetTestClass = New instance of class(strClassName)

End Function

Answer 1

There's no reflection in VBA, so I don't think this is possible. You'd have to do something like the following I'm afraid:

Function GetTestClass(lngClassNo as long) as Object

    Select Case lngClassNo
    Case 1
        Set GetTestClass = New CTest1
    Case 2
        Set GetTestClass = New CTest2
    ...

    End Select

End Function

Unless that is your CTest classes are defined in a COM DLL, in which case you could use the CreateObject statement. You would need to use VB6 to create such a DLL though, you can't create DLLs in Excel, Access, etc.

Function GetTestClass(lngClassNo as long) as Object

    Set GetTestClass = CreateObject("MyDll.CTest" & lngClassNo)

End Function

Answer 2

VB class definitions are really defining COM interfaces behind the scenes, so one can define data types as an abstract interface definition with concrete implementations using the implements keyword.

To get any sort of polymorphism you have to do this, otherwise you will have problems with casting. It is somewhat fiddly but technically possible to do this with VB. If you want to dig into it find some of the advanced VB books by Dan Appleman or Matthew Kurland. I'm not sure if they're still in print but they're probably available through Amazon Marketplace.

This works with VB6 and I'm fairly sure it works with VBA.

Answer 3

You might be able to do it with a collection class or object array. All the objects are in one array.

In your class have a .Name property and when you create an instance of it do this:

Dim CTest() as New CTest
For n = 1 to 10
    Redim Preserve CTest(n)
    CTest(n).Name = "CTest" & CStr(n)
Next l

Quick and dirty. The above example would return 10 CTest objects in a single object array. You could also ditch the .Name and just use CTest(n).

Answer 4

You've lost me a bit with this: how can polymorphism help you to create instances of a class using just a string?

I want to be able to look up in a table which class has to be used with a selected option. So how do you do this without using a select-statement?