How to call a sub/function using a variable holding the sub/function name?

[beacon]

Hi everybody,

[Access 2010]

I'm not sure if this is possible or not, but I wanted to check to be sure.

I'm working with a database that depends on running a bunch of tables through a number of different queries (including make-table queries). This happens a number of different times throughout the database as data is compiled for different sections.

Due to the inefficiency of this process, I'm trying to automate everything. Seeing as how the process depends heavily on the order of operations, I thought that creating a Main() subroutine that would call all the other subroutines/functions would be a good way to keep things organized.

Another thing I'm trying to do to debug is print a message when the next subroutine in the process is run with a little message about the status. I thought that I might be able to pass the subroutine/function name to another subroutine/function as a string, use the string in a "debug.prin t" to display a message, then use Call with the string variable to call the subroutine/function.

So, here's an example of what I'm trying to do:

'Main subroutine
'''''''''''''''''
Public Sub Main()

    StartSub "SelectProviders"

    StartSub "RunProviderUpdate"

End Sub

'StartSub subroutine
'''''''''''''''''''''
Public Sub StartSub(strSubroutine as String)

    Debug.Print strSubroutine & " started..."
    Call strSubroutine
    Debug.Print strSubroutine & " complete."

End Sub

I get a compile error at "Call strSubroutine". Is there anything I can use to replace this with so I can call the subroutine and still use the string in "Debug.Prin t"??

Thanks,
beacon

[NeoPa]

I don't believe this is supported in Access (Though Excel has an Eval() procedure that may be used for such I believe).

[Rabbit]

I think Access has the same function but I don't have it at home to test.

[NeoPa]

Indeed so Rabbit. In the Help page (2003) I found the following :

Help:
The following example assumes that you have a series of 50 functions defined as A1, A2, and so on. This example uses the Eval function to call each function in the series.

Sub CallSeries()
    Dim intI As Integer

    For intI = 1 To 50
        Eval("A" & intI & "()")
    Next intI
End Sub

[beacon]

@NeoPa

Were you able to get it to work for my example? I've tried the Eval() function a couple of different ways, such as passing a string/variant to the subroutine and using that variable in the Eval() function with the Call statement (I get error 2482 - Microsoft Access cannot find the name 'SelectProvider s' you entered in the expression).

Public Sub StartSub(strSubroutine As Variant)
    
    Debug.Print strSubroutine & " started..."
    Call Eval(strSubroutine)
    Debug.Print strSubroutine & " complete." & vbCrLf

End Sub

[TheSmileyCoder]

I did a bit of testing and found:
I could only get it to work if the function call includes () and it must be a function not a sub. Example code shown below:

Public Sub RunProc(strProc As String)
 Eval (strProc & "()")
End Sub
   
Public Function TestProc()
   Debug.Print "It worked"
End Function

Testing with

RunProc("testproc")

in the immediate pane worked fine.

[NeoPa]

As Smiley highlights, your code is not similar to that in the Help page, in that it leaves out the parentheses at the end "()". Also, you don't include the code of your procedure at all, so it's hard to know if it's a Function procedure or a Sub-routine procedure. The parameter name of strSubroutine, which is Dimmed as a Variant, tells me not to rely on your naming as an indication of what it is, but it would certainly need to be a function if you want it to work, and probably a Publicly visible one too.

Here's something that looks like what you need (It will still only work if provided with the name of a valid Function procedure) :

Public Sub StartFunc(ByVal strFunction As String)

    strFunction = strFunction & "()"
    Debug.Print strFunction & " started..."
    Call Eval(strFunction)
    Debug.Print strFunction & " complete."

End Sub

[TheSmileyCoder]

A quick tested indicated that any function to be called through the use of Eval() must be public (As expected).

[NeoPa]

Thank you for that Smiley. I guessed that Eval() is similar in that respect to running SQL code, in that it doesn't have any concept of where it's called from and only has access to items that are available to it within the library it runs from (Access.Applica tion in the case of Eval()).