Function name as argument to subroutine 1

[Galileo]

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?

 

[jproj]

Galileo:

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

 

[Galileo]

jproj,

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

 

[jproj]

Galileo,

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

 

[yakpol]

Galileo,
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

 

[yakpol]

Actually it was easier then I thought. The following code is selfexplanatory. Use help on Application.Run function for more info.

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

 

[Galileo]

Yakov,

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&quot
Call DoSomethingWithFunction(x2,"FunctionName2&quot
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

 

[IRstuff]

Could you explain why you need to do a double function call?

If you already know the name of the function and the parameters, why can't you call the function directly? TTFN

 

[yakpol]

IRstuff,
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.

 

[cactus13]

Would it be possible to use something like this, but then for subroutines in combination with (macro)buttons in a form?

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

 

[Lalbatros]

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.