How to call a procedure usign variable?

[Mathematician]

Hi,

I have procedure:

sub test_thirst()

msgbox("This is test")

end sub

and I would like to call this procedure using a variable as a name of the procedure above, I mean sth like this:

sub run_variable()

My_Variable="test_thirst"

Call My_Variable

' Here my procedure test_thirst should to start but it is not. Do you
' have any suggestions?

end sub

Thank You in advance for any help.

Mathematician

 

[Dennisk]

I dont think this is possible in access. Although there is an Eval() function so it might be worth looking at this.

 

[Guus2005]

Nope, Eval doesn't work

Try the AddressOf operator. It returns the address of the procedure.
Let us know if it works.

 

[Pat Hartman]

I have done this with the eval function.

Call Eval(rsDAO2!Funcname) ' FuncName includes ending ()

This code is part of a loop that reads a query that contains a list of functions that need to be run. I don't know if this will work with subs but it does work with functions. Just make sure that the name passed to the Eval() includes the ().

 

[ChrisO]

G’day Mathematician and welcome to the site.

A procedure, subroutine or function, can be executed by the Application.Run method: -

Option Explicit
Option Compare Text


Sub test_thirst()

    MsgBox "This is test"

End Sub


Function XYZ() As String

    XYZ = "Foo was here."
    
End Function


Sub run_variable()
    Dim My_Variable As String

    My_Variable = "test_thirst"

    Application.Run My_Variable
    
    My_Variable = "XYZ"
        
    MsgBox Application.Run(My_Variable)

End Sub

Hope that helps.

Regards,
Chris.

 

[ChrisO]

G’day Mathematician.

A small database (in A97) is attached if you wish to play with the Eval() function.

Regards,
Chris.

 

[Mathematician]

Thank You,

You are a genius

The problem is solved.

One more time thank You.

Mathematician

 

[Guus2005]

Yes. It worked using the Eval function

?Eval(Chr$(39) & "vb" & strColor & Chr$(39))
vbWhite

 

[Banana]

I'm kind of confused.

I tried the following:

prpstr="foo"

Call foo  '<--- Works
Application.Run prpstr '<--- Can't find the procedure
Call Eval(prpstr) '<--Same Error as above

public function foo()

end function

I already tried using foo with the () at end and declaring foo as sub to no avail. I also made sure I saved the changes to the module and form where the module is located. I feel that I am missing something very basic here...


Edit: Some further experiments, I found that if I put the foo in a module, it works OK, but only once (e.g. it errors on the Call line after Application.Run worked). I also tried to fully qualify the foo as it was in form module, e.g. Forms!MyForm.foo but it didn't take either. I would like to keep this within one module which happens to be a form module and don't mind qualifying the full references if possible. It's so strange that when I put in foo (as a procedure, not a string) in the Application.Run() and Eval(), it works fine, even in same form module but when it's a string, Access can't find it. WTF?

 

[raskew]

Probably not working because because str() is an Access function.

This does work:

X = "test_thirst"
application.Run x


Bob

 

[Banana]

I actually didn't use a str- it was strprp, but I had forgotten that when I wrote that out.

I also tried dimming a new variant with name of x, but it still can't find the procedure.

 

[raskew]

Ensure the sub is declared as Public and stored in a standard module.

Public Sub test_thirst()

MsgBox ("This is a test")

End Sub

 

[Banana]

raskew, yes, that worked.

As I said above, I wanted to see if I can call this within single form module. If I pass the name of procedure like this:

Application.Run foo
Call Eval(foo)

Both works fine except that after returning from the foo, it says it can't find procedure ".". If I fully qualify the reference to foo, I get an error 31005 saying that it's in a sandbox mode and cannot evaluate the expression.

 

[raskew]

Take a look here: http://office.microsoft.com/en-us/access/HA012301901033.aspx#4

It appears that Run is one of the commands blocked by the Sandbox mode.

Bob

 

[Banana]

Except.... that I don't have 2007. I'm just running 2003. This is what baffle me. I don't remember seeing anything about sandbox mode for 2003?

 

[raskew]

See http://www.databasedev.co.uk/jet-sandbox-mode.html. I'd never heard of it until I did a Google.

Bob

 

[gemma-the-husky]

banana

this should work like this

however - you can pass the variable name - but you have to get inside a string

so eval("foo")

or eval(chr(34) & strprp & chr(34))

so this forms the string (with the quote marks!) "foo" as a parameter for eval

note that you can include parameters so you can form a string

foo(myparam) etc

I wasnt able to get the params to return values, so it is not quite passing a full procedure address, but i found it very useful

 

[Banana]

Raskew-

Hmm. Interesting. I can understand why they would be concerned, but I am not following whether digital signing is supposed to fix the problem. The project is already signed with a self-signed certificate which is adequate because it's a company thing, and won't be distributed outside of the company.

I guess I'll just settle for two modules, then.

Gemma-

I'll have to try this. If this is true, this is simply most bizarre thing to do as this amounts to double quoting what should be passed as a string. But hey, if I can just keep everything in single module, then great! Thanks.


Edit: Gemma, I tested this and it did not call the function at all; it did evaluate OK, but the break didn't move to the foo() at all.

 

[gemma-the-husky]

my bad - i was doing this offhand from memory

you need the () at the end of the proc name

you need the quotes if you are putting parameters inside the function call


i've just tested these, and these work now!

Function FOO()
    MsgBox ("IN FOO")
End Function


Sub testeval()
Const mystr = "foo()"
    Eval ("foo()")
    Eval (mystr)
End Sub

 

[doco]

Take a look here: http://office.microsoft.com/en-us/access/HA012301901033.aspx#4

It appears that Run is one of the commands blocked by the Sandbox mode.

Bob

Using both 2k & 2k+3 and it works marvelously. Thanks ChrisO! I will keep that one in mind for the future...