By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

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

beacon
100+
P: 579
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.print" 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:
Expand|Select|Wrap|Line Numbers
  1. 'Main subroutine
  2. '''''''''''''''''
  3. Public Sub Main()
  4.  
  5.     StartSub "SelectProviders"
  6.  
  7.     StartSub "RunProviderUpdate"
  8.  
  9. End Sub
  10.  
  11. 'StartSub subroutine
  12. '''''''''''''''''''''
  13. Public Sub StartSub(strSubroutine as String)
  14.  
  15.     Debug.Print strSubroutine & " started..."
  16.     Call strSubroutine
  17.     Debug.Print strSubroutine & " complete."
  18.  
  19. End Sub
  20.  
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.Print"??

Thanks,
beacon
Nov 8 '12 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub RunProc(strProc As String)
  2.  Eval (strProc & "()")
  3. End Sub
  4.  
  5. Public Function TestProc()
  6.    Debug.Print "It worked"
  7. End Function
  8.  
Testing with
Expand|Select|Wrap|Line Numbers
  1. RunProc("testproc")
in the immediate pane worked fine.

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I don't believe this is supported in Access (Though Excel has an Eval() procedure that may be used for such I believe).
Nov 9 '12 #2

Rabbit
Expert Mod 10K+
P: 12,315
I think Access has the same function but I don't have it at home to test.
Nov 9 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.

Expand|Select|Wrap|Line Numbers
  1. Sub CallSeries()
  2.     Dim intI As Integer
  3.  
  4.     For intI = 1 To 50
  5.         Eval("A" & intI & "()")
  6.     Next intI
  7. End Sub
Nov 9 '12 #4

beacon
100+
P: 579
@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 'SelectProviders' you entered in the expression).

Expand|Select|Wrap|Line Numbers
  1. Public Sub StartSub(strSubroutine As Variant)
  2.  
  3.     Debug.Print strSubroutine & " started..."
  4.     Call Eval(strSubroutine)
  5.     Debug.Print strSubroutine & " complete." & vbCrLf
  6.  
  7. End Sub
  8.  
Nov 9 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub RunProc(strProc As String)
  2.  Eval (strProc & "()")
  3. End Sub
  4.  
  5. Public Function TestProc()
  6.    Debug.Print "It worked"
  7. End Function
  8.  
Testing with
Expand|Select|Wrap|Line Numbers
  1. RunProc("testproc")
in the immediate pane worked fine.
Nov 9 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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) :
Expand|Select|Wrap|Line Numbers
  1. Public Sub StartFunc(ByVal strFunction As String)
  2.  
  3.     strFunction = strFunction & "()"
  4.     Debug.Print strFunction & " started..."
  5.     Call Eval(strFunction)
  6.     Debug.Print strFunction & " complete."
  7.  
  8. End Sub
Nov 9 '12 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
A quick tested indicated that any function to be called through the use of Eval() must be public (As expected).
Nov 9 '12 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.Application in the case of Eval()).
Nov 10 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.