VBA function call

Is there a way to call a function, where the call is stored in a table

**Record 1  task            Function call**
124567      Email customer  Call function emailcus(a,b,c,d)
434535    开发者_如何学C  AddCost         Call function addcost(a,b,c,d)

Cheers

Graham

Yes, you can use the Eval() function for that.

Syntax:

Dim ReturnValue As String

ReturnValue = Eval("MyFunction(1, 2)")

Note that you have to provide the exact function call including parameters.

I'm pointing this out because I'm not sure if the parameters a, b, c, d in your example are only dummy values for your example, or if you expect VBA to fill in the values of some variables named a, b, c, d automatically.
The Eval function does not do this, so if you need variable values as parameters, you would have to do something like this:

Dim ReturnValue As String
Dim EvalString As String

EvalString = "MyFunction(" & Variable1 & ", " & Variable2 & ")"

ReturnValue = Eval(EvalString )

This is a variation on the answer already given by haarrrgh, so if you find it useful be sure to upvote that one as well.

There's another way to deal with placeholders in your DB-stored function calls. First, change your data thusly:

**Record 1  task            Function call** 
124567      Email customer  Call function emailcus([TokenA],[TokenB])
434535      AddCost         Call function addcost([TokenA],[TokenB])

Note that the [SquareBrackets] are not actually required syntax in this example, just something that I tend to use in this situation. The important part is to make the parameter tokens something that doesn't appear elsewhere in the string value (including other tokens). You can use as many parameters as you need, just make sure that the calling code knows about how many are expected by each function-call string (I cut it down to shorten my following code).

Then when it's time to call your function, do this:

Dim ReturnValue   As String    'or as appropriate for individual the function's return
Dim EvalString    As String

EvalString = 'code to fetch from table

EvalString = Replace(EvalString, "[TokenA]", strValueA) 'strValueA passed in?
EvalString = Replace(EvalString, "[TokenB]", strValueB) 'strValueB passed in?

ReturnValue = Eval(EvalString)

In VB6, at least (so I assume it's true in VBA), Replace is faster than concatenation. I also find this more readable, but that may be because I'm used to it from using a similar technique to build SQL commands in code (using Const declarations rather than DB storage, but that would work too).

EDIT

As I reread my "finished" post just after submitting it, I realized that there's a gotcha lurking in there. Because you're doing substitution before submitting the string to Eval, these are actual values that are being put into the string, not variables. The code I presented above works fine if your parameters are numeric, but if they're String type you have to include the quotes, either in your data or in your Replace call. I prefer the former, so change your data to this:

**Record 1  task            Function call** 
124567      Email customer  Call function emailcus('[TokenA]','[TokenB]')
434535      AddCost         Call function addcost('[TokenA]','[TokenB]')

This works as tested with a Const. Stored in a DB record, you might need this instead:

**Record 1  task            Function call** 
124567      Email customer  Call function emailcus(""[TokenA]"",""[TokenB]"")
434535      AddCost         Call function addcost(""[TokenA]"",""[TokenB]"")

(which also works with a Const...).

The alternative is to leave the data as it is in my first part, & change the Replace calls:

EvalString = Replace(EvalString, "[TokenA]", """" & strValueA & """") 'strValueA passed in?
'or maybe
EvalString = Replace(EvalString, "[TokenB]", "'" & strValueB & "'") 'strValueA passed in?

A couple of other potential gotchas: These must be Functions, not Subs, and they must be declared Public in a module, not in a Form's code.