Function name as argument to subroutine
Function name as argument to subroutine
(OP)
Is it possible, in VBA, to pass the name of a function (or a subroutine) to a subroutine through the argument list? In FORTRAN it's possible to have a function name as one of the arguments of a subroutine. For example, when writing a subroutine to find the roots of an arbitrary function, one typically does something like this:
SUBROUTINE rootfinder(root,guess1,guess2,functionname)
declarations, etc.
a=functionname(guess1)
b=functionname(guess2)
etc.
I haven't had any luck doing this in VBA. Does anyone know how to do this, or if there's a convenient work-around?
SUBROUTINE rootfinder(root,guess1,guess2,functionname)
declarations, etc.
a=functionname(guess1)
b=functionname(guess2)
etc.
I haven't had any luck doing this in VBA. Does anyone know how to do this, or if there's a convenient work-around?





RE: Function name as argument to subroutine
I'm kind of confused as to what you are trying to do... In VBA, you don't need to use the function name in the argument list of the subroutine in order to call the function... for example if you wanted to call a function w/in a subroutine and then display the result, it might look something like this:
Sub RootFinder(guess1, guess2)
'....declarations
A = Root(guess1) '(Where "Root" is your function name)
B = Root(guess2)
Msgbox "Guess #1 Returned a value of: " & A
Msgbox "Guess #2 Returned a value of: " & B
.
.
.
.
End Sub
Function Root(guess)
Root = guess * 2 + 3.... (math manipulations, etc.)
end Function
Note: the values of "guess1" and "guess2" must be passed from another subroutine.
If I have missed the point, let me know.
jproj
RE: Function name as argument to subroutine
Thanks for your reply. I realize it isn't necessary to use the function name in the argument list in order to call it from the subroutine. It would just make the subroutine more general if the name could be a "variable" identified in the argument list.
For example, if you needed to find the roots of more than one function during the execution of a procedure, you would be able to use the same root finding routine if the function name were an argument. The way you suggest (the same way I'm doing it now, by the way) requires that you duplicate the root finding subroutine and change the name of the function for every copy you need. Cumbersome.
Galileo
RE: Function name as argument to subroutine
I'm confused as to why you have to change the name of the function every time you need to run it... you shouldn't have to do that. As long as the equations you use in the function are the same, you only need one function... just call it multiple times with different variables.... for example, if you wanted to know the square of several variables...
Sub Square(Number1, Number2, Number3,....)
A = SQRCalc(Number1)
B = SQRCalc(Number2)
C = SQRCalc(Number3)
.
.
.
End Sub
Function SQRCalc(Variable)
SQRCalc = Variable^2
End Function
Now, (back to root finding) if you had different equations (2nd order, 3rd order... etc) you can still use a single function, you just have to use some logic inside the function to determine which equations to use.
If I am still misunderstanding, maybe you could post some code and I could look at how you are currently running the program.
jproj
RE: Function name as argument to subroutine
A year ago I was rewriting some functions from the 'Numerical Recipies in C' for Excel VBA, and found the way to call the function name as an argument. I'll get you the code from my home computer later today. (My memory is fading, don't remember the function name in my head any longer!)
Yakov
RE: Function name as argument to subroutine
Sub test()
MsgBox "square of 3 = " & Application.Run("square", 3)
MsgBox "cube of 3 = " & Application.Run("cube", 3)
End Sub
Function square(x)
square = x ^ 2
End Function
Function cube(x)
cube = x ^ 3
End Function
RE: Function name as argument to subroutine
Yes! That's just what I needed. Thanks.
To wrap this up for others who may be interested, Yakov's solution (applied to my application) would look something like this:
Sub MainProcedure()
Call DoSomethingWithFunction(x1,"FunctionName1")
Call DoSomethingWithFunction(x2,"FunctionName2")
End Sub
Sub DoSomethingWithFunction(arg1,Func as String)
MsgBox Application.Run(Func,arg1)
End Sub
Function FunctionName1(x)
FunctionName1=arbitraryfunction1(x)
End Function
Function FunctionName2(x)
FunctionName2=arbitraryfunction2(x)
End Function
Galileo
RE: Function name as argument to subroutine
If you already know the name of the function and the parameters, why can't you call the function directly?
TTFN
RE: Function name as argument to subroutine
For example, I wrote a routine to minimize the function (any function). So, instead of rewriting the whole routine for each function I just use the function name as an argument. Like Excel goalseek and solver find solution for any function you create.
RE: Function name as argument to subroutine
It would save me a lot of code if I could specify arguments while I define only a button and the assignment with arguments.
Thanks in advance!
Jonathan
RE: Function name as argument to subroutine
Yet another way to solve it: functions as objects,
assume a minimization algorithm,for example,
- define each function in its own Class
one member of this class could be called theFunc:
class fct1 --> Function theFunc() ... End Function
class fct2 --> Function theFunc() ... End Function
...
- the 'minim' algorithm should take an object as argument:
sub minim(theFuncToMinim as Object, ...)
..
x = theFuncToMinim.theFunc()
..
End Sub
- call the algorithm like this:
Set afct2 = new fct2
...
minim afct2,...
This method is very powerful, for example, you can work with several instances of a function-object (for different parameters). This method can be developed in much more general ways.