×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Function name as argument to subroutine

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?

RE: Function name as argument to subroutine

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

RE: Function name as argument to subroutine

(OP)
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

RE: Function name as argument to subroutine

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

RE: Function name as argument to subroutine

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

RE: Function name as argument to subroutine

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

RE: Function name as argument to subroutine

(OP)
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")
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

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

RE: Function name as argument to subroutine

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.

RE: Function name as argument to subroutine

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

RE: Function name as argument to subroutine

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources