excel - 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

CallByName function can help you. Let's say there are some class modules in your project: clsSample0, clsSample1 and clsSample2. Add a new class module named clsSpawner, which lists all target classes as public variables having the same names, and declared with New keyword:

Public clsSample0 As New clsSample0
Public clsSample1 As New clsSample1
Public clsSample2 As New clsSample2

In a standard module add Function Spawn() code:

Function Spawn(sClassName) As Object

    Set Spawn = CallByName(New clsSpawner, sClassName, VbGet)

End Function

Test it with some code like this:

Sub TestSpawn()

    Dim objSample0a As Object
    Dim objSample0b As Object
    Dim objSample1 As Object
    Dim objSample2 As Object

    Set objSample0a = Spawn("clsSample0")
    Set objSample0b = Spawn("clsSample0")
    Set objSample1 = Spawn("clsSample1")
    Set objSample2 = Spawn("clsSample2")

    Debug.Print TypeName(objSample0a)            ' clsSample0
    Debug.Print TypeName(objSample0b)            ' clsSample0
    Debug.Print objSample0a Is objSample0b       ' False
    Debug.Print TypeName(objSample1)             ' clsSample1
    Debug.Print TypeName(objSample2)             ' clsSample2

End Sub

How does it work? Spawn function instantiates clsSpawner and calls the clsSpawner instance to return requested property, and actually clsSpawner instance creates a new instance of the target class due to declaration with New keyword and returns the reference.