Passing a Function name as a procedure argument

[wjg]

1. I have a Standard Module Function:
Public Function GetARoot(ByVal FirstGuess As Double, ByVal
FunctionName As _
String) As Double
that calculates the root of an equation.

I have several equations programmed as Functions in the Standard
Module. I wish to pass the name of one of these Functions as an
argument to "FunctionName" and call the Function with this name from
inside GetARoot.

Can this be done?

 

[Bob Phillips]

Not if I understand you correctly, but you could pass an id and check that
with select Case and run the function accordingly

Select Case thisId
Case 1: myVal = Func1(a,b)
Case 2: myVal = Func2(a,b)
'etc.
End Select

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[wjg]

Thanks Bob, for a good robust solution. But would it be possible to do
it more elegantly like:

myValy= Fun1(a,b)

where "Fun1(a,b)" is passed in as a string or an object in the
parameter list?

Then if I write an new procedure, Fun3(c,d), I will not have to add it
to a case statement in the GetARoot procedure.

 

[Dana DeLouis]

This is probably bad programming practice, but are there any ideas here that
can help?
Just step thru "TestIt" to follow the basic ideas. Good luck.

Sub TestIt()
'// Just call BasicFx
Debug.Print BasicFx("MyFx1", 2, 3)
Debug.Print BasicFx("MyFx2", 2, 3)
End Sub

Function BasicFx(Fx As String, x, y)
BasicFx = Application.Run(Fx, x, y)
End Function

Function MyFx1(a, b)
MyFx1 = a + b
End Function

Function MyFx2(a, b)
MyFx2 = a ^ 2 + b
End Function

HTH. :>)

 

[Bob Phillips]

No, that is exactly the point I was making, you cannot do that.

Here is another alternative, but only becomes useful I guess if all
functions have the same number of arguments

Sub TestFuncEvaluator()
Dim sFunction As String

sFunction = "func1"
Debug.Print Application.Run(sFunction, 11, 22)

sFunction = "func2"
Debug.Print Application.Run(sFunction, 11, 22)


End Sub

Function func1(val1, val2)
func1 = val1 * val2
End Function

Function func2(val1, val2)
func2 = val1 + val2
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mark H. Shin]

WJG,

A good way to do this is to place your function(s) in a class module. Then
you can use the CallByName VBA method to "call" the desired function using a
string value...

Module Module1:
Sub test()
Dim M As New MathLibrary

CallByName M, "FuncA", VbMethod, 2345, 4321
CallByName M, "FuncB", VbMethod, 2345
End Sub

Class MathLibrary:
Public Function FuncA(a As Integer, b As Integer)
MsgBox "FuncA(" & a & "," & b & ")"
End Function
Public Function FuncB(a As Integer)
MsgBox "FuncB(" & a & ")"
End Function

BJ

 

[Mark H. Shin]

Sorry for the double post but the following example is bit more to what you
need:

Module Module1:
Sub test()
Dim M As New MathLibrary

result1 = CallByName(M, "FuncA", VbMethod, 2345, 4321)
result2 = CallByName(M, "FuncB", VbMethod, 2)
End Sub


Class MathLibrary:
Public Function FuncA(a As Double, b As Double) As Double
FuncA = a / b
End Function
Public Function FuncB(a As Double) As Double
FuncB = a ^ 2
End Function

 

[Dave Peterson]

And I think you need xl2002 or higher to use CallByName.

 

[Bob Phillips]

Nah, 2000 has it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Thanks for the correction.

Nah, 2000 has it.

--

HTH

RP
(remove nothere from the email address if mailing direct)