Create a new object using the text name of the class

by

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.

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. Open Excel file for reading with VBA without display
  7. Wait for shell command to complete [duplicate]
  8. Excel VBA App stops spontaneously with message “Code execution has been halted”
  9. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  10. How to remove all non alphanumeric characters from a string except period and space in excel?
  11. Find last used cell in Excel VBA
  12. Does Dir() make any guarantee on the order of files returned?
  13. Call a Sub with a String
  14. How to clear memory to prevent “out of memory error” in excel vba?
  15. Assign on-click VBA function to a dynamically created button on Excel Userform
  16. Using a wildcard to open an excel workbook
  17. VBA: Usage of parentheses for a method
  18. Fully reference a worksheet by codename
  19. ADODB Connection String for .csv
  20. OnTime for less than 1 second without becoming Unresponsive
  21. How to clear memory to prevent “out of memory error” in VBA?
  22. Display milliseconds in Excel
  23. VBA editor auto-deletes spaces at the ends of lines
  24. Select entire column in table using Excel VBA
  25. Application.Quit command not closing the entire Excel Application
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Trying to open the workbook in separate instance
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment