Using variables as function names and parameters in VBA

Question

I have the following variables in my VBA logic sFunctionName, sParam1, sParam2, sParam3 all string variables populated from a table. I would like to use those variables to call a function. I have tried using Application.Run(sFunctionName, sParam1) and the statement fails, However when i use Application.Run(sFunctionName) it works just fine. I have also tried Eval on someones suggestion with no luck. Can someone let me know what I am missing, or if i can even do what I am attempting to do? I appreciate any and all help.

Thanks, J

No I did not, and that was infact the error. Once I removed the braces all went smooth. Thanks! — Jay
– Jay, Commented Dec 22, 2010 at 16:37
I'm glad that helped. I've added the answer to this question. You could mark it as accepted to make this question useful for others. — Marek
– Marek, Commented Dec 23, 2010 at 8:17
Application.Run(Arg1, Arg2....) is a function and returns a value. Application.Run Arg1, Arg2... is a subroutine call that doesn't return a value. So, if you use Call Application.Run leave out the parens. If you use MyVariable = Application.Run then you need to use the parens. This 100% consistent behavior across VBA and always has been. — David-W-Fenton
– David-W-Fenton, Commented Dec 24, 2010 at 2:34
@David-W-Fenton--That's almost right, but you got tripped up by one of the VB(A) weirdnesses: Yes, you call a Sub without parens on the parameters... unless you use the call keyword, in which case you must use the parens. I'm sure you know this already when you're not dashing off a comment, but others reading the comments may not. I'll try to find the really good article on parens in VB(A) that I read recently & thought I'd bookmarked.... — RolandTumble
– RolandTumble, Commented Jan 4, 2011 at 22:12

PowerUser · Accepted Answer · 2010-12-22 16:20:52Z

2

#1. Regarding Eval(): Per Access's help files,

You can use the Eval function to evaluate an expression that results in a text string or a numeric value.

So, if your function resolves to text or numeric, then you're good to go.
i.e. Debug.Print Eval("Date()")

#2. I don't think your problem with Run() is with the actual function itself, but rather how you are applying it. I threw together some quick code. Does this help?

Function AddOne(What As Integer) As Integer
    AddOne = What + 1
End Function

Function x()
    Dim WhichFunc As String
    WhichFunc = "AddOne"
    Dim What As Integer
    What = 1
    x = Run(WhichFunc, What)
End Function

(Calling this with debug.print x in the Immediate Window will give you a 2)

answered Dec 22, 2010 at 16:20

PowerUser

11.8k20 gold badges67 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jay Over a year ago

This code did help me to better understand the run method. While it was not exactly what I was looking for it was def a help. Thank you!

PowerUser Over a year ago

That's how these things go, sometimes. Welcome to SO!

David-W-Fenton Over a year ago

I don't see how this has anything at all to do with the original question, where the problem is entirely due to not distinguishing the two distinct ways one calls a Function vs. a Subroutine in VBA.

Marek · Accepted Answer · 2010-12-23 08:15:50Z

1

Try running a method without braces, like:

Application.Run sFunctionName, sParam1

answered Dec 23, 2010 at 8:15

Marek

7076 silver badges14 bronze badges

Collectives™ on Stack Overflow

Using variables as function names and parameters in VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related