In VBA, can you call a function based on a String input?

My basic question is whether or not it is possible in VBA to call a function depending on a string input.  For example:

CALL_FUNCTION("My_Function_Name", args())

I think this is possible in VB6, but I haven't been able to get anything to work in VBA.

To provide context...I have an application I am writing in Excel.  The application requires that a Book object is created, and the book object consists of Chapters, Pages etc.  To create the Book object, I am importing data from potentially multiple sources.  Currently it is either a database or an Excel Spreadsheet.  The first snippet of code shows how I create the Book object (WriteBookObj).  The second and third code snippets are used to either locate the data in either a database (DB) or excel spreadsheet (XL).

I wanted to create a generalized function to build the Book object (which I have achieved with WriteBookObj).  The idea was then to point the generalized functions within WriteBookObj to either the DB module or the XL module, depending on the source I wanted to use.  This *could* be achieved quite easily if I could call a function in the following way (for example):

In WriteBookObj:
Set chap = CreateChapterObjShell("DB", bk.UniqueID, iChap)

And then, using my made up function CALL_FUNCTION, which I hope exists in some form::
Public Function CreateChapterObjShell(ByVal FunctionSource as String, ByVal bkId As Integer, ByVal iChap As Integer) As CChapter
    Set CreateChapterObjShell = CALL_FUNCTION("CreateChapterObj_" & FunctionSource, (bkId, iChap))
End Function

Currently, the only way I can switch between sources (DB or XL) is by commenting out either the second or third snippet below, depending on which I do not need.  I have to comment them out as they have the same function names, as defined in WriteBookObj.

Any insights into my problem would be greatly appreciated.

' ==== Building the Book object ====
 
 
Public Function WriteBookObj(ByRef bk As CBook) As CBook
 
    Dim chap As CChapter, pg As CPage, ch As CChart, se As CSeries
    Dim iChap, ipg, ich, ise As Integer
    
    For iChap = 1 To bk.nChapters
    
        Set chap = CreateChapterObjShell(bk.UniqueID, iChap)
    
        ' Loop through each Page
        For ipg = 1 To chap.nPages
            
            ' Create Page
            Set pg = CreatePageObjShell(chap.UniqueID, ipg)
            
            ' Loop through each Chart
            For ich = 1 To pg.nCharts
                
                ' Create Chart
                Set ch = CreateChartObjShell(chap.Number, pg.UniqueID, pg.Number, ich)
                ' Loop through each Series
                For ise = 1 To ch.nSeries
                    ' Create the series
                    Set se = CreateSeriesObjShell(ipg, ch.UniqueID, ich, ise)
                    ' Add the Series to the Chart
                    ch.AddSingleSeries se
                Next
                
                ' Add the Chart to the Page
                pg.AddSingleChart ch
            
            Next
            
            ' Add the Page to the Sheet
            chap.AddSinglePage pg
            
        Next
        
        ' Add the sheet to the Book
        bk.AddSingleChapter chap
        
    Next
 
    Set WriteBookObj = bk
 
End Function
 
 
 
' ===== Code in First Module "DB" =====
 
 
Public Function CreateChapterObjShell(ByVal bkId As Integer, ByVal iChap As Integer) As CChapter
    Set CreateChapterObjShell = CreateChapterObj_DB(bkId, iChap)
End Function
Public Function CreatePageObjShell(ByVal chapId As Integer, ByVal pgNum As Integer) As CPage
    Set CreatePageObjShell = CreatePageObj_DB(chapId, pgNum)
End Function
Public Function CreateChartObjShell(ByVal chapNum As Integer, ByVal pgId As Integer, ByVal pgNum As Integer, ByVal chNum As Integer) As CChart
    Set CreateChartObjShell = CreateChartObj_DB(chapNum, pgId, pgNum, chNum)
End Function
Public Function CreateSeriesObjShell(ByVal pgNum As Integer, ByVal chId As Integer, ByVal chNum As Integer, ByVal seNum As Integer) As CSeries
    Set CreateSeriesObjShell = CreateSeriesObj_DB(pgNum, chId, chNum, seNum)
End Function
 
 
' ===== Code in Second Module "XL" =====
 
 
Public Function CreateChapterObjShell(ByVal bkId As Integer, ByVal iChap As Integer) As CChapter
    Set CreateChapterObjShell = CreateChapterObj_XL(bkId, iChap)
End Function
Public Function CreatePageObjShell(ByVal chapId As Integer, ByVal pgNum As Integer) As CPage
    Set CreatePageObjShell = CreatePageObj_XL(chapId, pgNum)
End Function
Public Function CreateChartObjShell(ByVal chapNum As Integer, ByVal pgId As Integer, ByVal pgNum As Integer, ByVal chNum As Integer) As CChart
    Set CreateChartObjShell = CreateChartObj_XL(chapNum, pgId, pgNum, chNum)
End Function
Public Function CreateSeriesObjShell(ByVal pgNum As Integer, ByVal chId As Integer, ByVal chNum As Integer, ByVal seNum As Integer) As CSeries
    Set CreateSeriesObjShell = CreateSeriesObj_XL(pgNum, chId, chNum, seNum)
End Function

Select allOpen in new window