[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

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

Open in new window

0
billelev
Asked:
billelev
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the function to call a public method/property of a class is named "CallByName".

is that what you are looking for?
0
 
Rory ArchibaldCommented:
If you use Application.Run then you pass the procedure name as a string plus any arguments you require. However, if you only have two options, a simple If statement would seem more efficient to me.
Regards,
Rory
0
 
billelevAuthor Commented:
Would you mind elaborating further, Rory?  I have seen that function before but I could not see exactly how to use it.

This is the syntax for the function:

CallByName(object, procname, calltype,[args()])

Can a module be an object?  That is where the function/process is located.

I also agree that an if statement would be efficient in this case, but I want to make this as scalable as possible.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can a module be an object?  
no. it must be a class or form.
0
 
Rory ArchibaldCommented:
It would be something like:
Set CreateChapterObjShell = Application.Run("CreateChapterObj_" & FunctionSource, bkId, iChap)

 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now