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

Using variables as function names and calling them?

P: n/a
I've got a field in a table that keeps the name of functions I need to
run.

I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).

How can I get VBA to run the desired function?

I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.

This is driving me nuts. Does anyone have a solution? I'm using
Access2002.

Oct 10 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
ManningFan wrote:
I've got a field in a table that keeps the name of functions I need to
run.

I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).

How can I get VBA to run the desired function?

I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.

This is driving me nuts. Does anyone have a solution? I'm using
Access2002.
Have you tried the Eval() function?

Eval(yourvar)

--
Roy-Vidar
Oct 10 '07 #2

P: n/a
I don't think VBA can do that in the way you want. Your asking it to edit
the program and compile on the fly. One way to simulate it, would be to
write a function using a case statement with each of the functions you want
to run in a case. Of course you would be limited to the functions programmed
into the case statement.

"ManningFan" <ma********@gmail.comwrote in message
news:11*********************@r29g2000hsg.googlegro ups.com...
I've got a field in a table that keeps the name of functions I need to
run.

I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).

How can I get VBA to run the desired function?

I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.

This is driving me nuts. Does anyone have a solution? I'm using
Access2002.

Oct 10 '07 #3

P: n/a
On Oct 10, 5:26 pm, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
ManningFan wrote:
I've got a field in a table that keeps the name of functions I need to
run.
I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).
How can I get VBA to run the desired function?
I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.
This is driving me nuts. Does anyone have a solution? I'm using
Access2002.

Have you tried the Eval() function?

Eval(yourvar)

--
Roy-Vidar
Tried that. I get that same error message.

I ended up making a macro to run each function, and then I used
DoCmd.RunMacro MCR, but I was really hoping that there was a more
graceful way.

Oct 10 '07 #4

P: n/a
On Oct 10, 5:18 pm, ManningFan <manning...@gmail.comwrote:
I've got a field in a table that keeps the name of functions I need to
run.

I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).

How can I get VBA to run the desired function?

I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.

This is driving me nuts. Does anyone have a solution? I'm using
Access2002.
for a sub

Sub temp()
MsgBox 1
End Sub

Sub temp2()
Dim s$
s = "temp"
Application.Run s
End Sub

----
for a function

Function temp3$(ByVal vS$)
temp3 = UCase(vS)
End Function

Sub temp4()
Dim s$
s = "temp3"
MsgBox Application.Run("temp3", "abc")
End Sub

Oct 10 '07 #5

P: n/a
lyle wrote:
On Oct 10, 5:18 pm, ManningFan <manning...@gmail.comwrote:
>>I've got a field in a table that keeps the name of functions I need to
run.

I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).

How can I get VBA to run the desired function?

I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.

This is driving me nuts. Does anyone have a solution? I'm using
Access2002.


for a sub

Sub temp()
MsgBox 1
End Sub

Sub temp2()
Dim s$
s = "temp"
Application.Run s
End Sub

----
for a function

Function temp3$(ByVal vS$)
temp3 = UCase(vS)
End Function

Sub temp4()
Dim s$
s = "temp3"
MsgBox Application.Run("temp3", "abc")
End Sub
I knew someone would have an esoteric command to do the trick. Good
examples, Lyle.

Oct 11 '07 #6

P: n/a
On Oct 10, 9:17 pm, Salad <o...@vinegar.comwrote:
I knew someone would have an esoteric command to do the trick. Good
examples, Lyle.
I knew about and used Application.Run for many years (maybe 8 or 9).
But I neglected to follow my own preachings, "Examine methods and
properties in the Object Browser", so when I read OP's message I
failed to see how it would return values, which I felt he might want
to do, as he specified that he wanted to run a function.
When I did look in the ObjectBrowser I found that Application.Run is a
function, not a sub as I had assumed.
So, we can use it to return values from the function we "run" as
Application.Run(FunctioName,ParamArrayofFunctionAr guments).
I think this makes Application.Run way more powerful than I had
realised previously.
So OP may use the suggestion, or he may not; but I have learned
something and thats prtty nice.

Oct 11 '07 #7

P: n/a
ManningFan wrote:
On Oct 10, 5:26 pm, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
>ManningFan wrote:
>>I've got a field in a table that keeps the name of functions I need to
run.
I do a DLookup on the table to retrieve the name of the function I
need to run (based on whatever criteria).
How can I get VBA to run the desired function?
I tried DoCmd.OpenFunction, I've tried using the Call command and I've
tried just sticking the variable in the code, among other things. In
most cases the variable will resolve to the proper function name, but
I will be prompted with an error saying that database can't find the
function. If I replace the variable with the function name returned,
it runs fine.
This is driving me nuts. Does anyone have a solution? I'm using
Access2002.
Have you tried the Eval() function?

Eval(yourvar)

--
Roy-Vidar

Tried that. I get that same error message.

I ended up making a macro to run each function, and then I used
DoCmd.RunMacro MCR, but I was really hoping that there was a more
graceful way.
This works here.

Function MyFunction1()
MsgBox "Blah!"
End Function

Sub MySub1()
Dim s As String

s = "MyFunction1()"
Eval s
End Sub

Function MyFunction2(ByVal v_s As String) As String
MyFunction2 = StrReverse(v_s)
End Function
Sub MySub2()
Dim s As String
Dim p As String

p = "Blah!"
s = "MyFunction2()"
s = Replace(s, "()", "(""" & p & """)")

MsgBox Eval(s)
End Sub

--
Roy-Vidar
Oct 11 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.