Forums  > Software  > How one may pass the function as a parameter to another function in VBA?  
Page 1 of 1
Display using:  


Total Posts: 3465
Joined: Jun 2004
Posted: 2005-04-13 20:52
To be more precise in Excel VBA.
A stupid and simple question, I hope.
In C++ it's so simple. Sad
Any help would be much appreciated...
P.S. I've googled already Dead

Нас ебут, а мы крепнем...


Total Posts: 1629
Joined: Jun 2004
Posted: 2005-04-13 21:25

To the best of knowledge it isn't so trivial. As far as I know you have to make your fuinction into an object and pass the object as a variant. So, for example:

In a vba module:

Public Sub bob()Dim f As Object
Set f = New int_sin
Dim Integ As Object
Set Integ = New RiemannIntegrator
Debug.Print Integ.Integrate(10, 20, f)
End Sub

In a vb(a) class module, or in a vb dll:

Class called int_sin, all it has is:

Public Function Eval(x As Double) As Double
  Eval = Sin(x)
End Function

Class called RiemannIntegrator, it has:

Option Explicit

Private i As Integer

Public Function Integrate(dfrom As Double, dto As Double, f As Variant) As Double
  'Simpson rule for integration
  Integrate = 0.5 * f.Eval(dfrom)
  For i = 1 To 99
    Integrate = Integrate + f.Eval(dfrom + i / 100 * (dto - dfrom))
  Next i
  Integrate = Integrate + 0.5 * f.Eval(dto)
  Integrate = Integrate / 100 * (dto - dfrom)
End Function

Of course, the disadvantage is that you aren't really passing the function: you have a list of all possible functions, each with its own class, and you are electing which one to use. This might be good enough though. And somebody else might know something better.

Graeme West


Total Posts: 1412
Joined: Jun 2004
Posted: 2005-04-13 23:47
quick and dirty way is to pass "FunctionName" as a string ,
then you can use either Application.Run "MyFunction" or Call "MyFunction"


Total Posts: 3465
Joined: Jun 2004
Posted: 2005-04-14 14:10
Or in Perl... sigh

Thank You very much, Graeme.
Your's is probably not the best way but it works!
And it saved me from of copying and pasting a lot.

silverside, I've memorised Your propostion, but it's a bit
too dirty (I need to call the same function 32 (and more) times).

Нас ебут, а мы крепнем...


Total Posts: 833
Joined: Jun 2004
Posted: 2005-04-14 20:35

I like Graeme's solution ...

And want to remember a rare word in Excel: "Evaluate".

As VBA is not compiled this gives a quite simplified way to
parse expressions, try Debug.Print (Evaluate("Sin( 4.2 )"))

This also works if you use a unique string for your variable,
replace it by a value and evaluate then (on a non-english PC
remember your language settings: decimal separators ...).

This can be carried further: if your function is Sin then
Sin(x) is called a function expression and in a symbolic
computer system (like Maple) it does not matter too much
whether you want to work with a function or an expression
as long as you know the variable: f and unapply(f(x),x)
are almost the same in Maple for a function f. This idea
can be translated to Excel:

As Excel has no parser just tell it that in "Sin( myX )"
the string myX has to be seen as variable. Thus instead
of working with function pointers (=adresses + type) as
in C one can work with function expressions as strings.

The advantage against "run"? You do not need to set up a own
function to input x^2. Disadvantage? String processing ...

It is possible to work with function pointers in Excel as
well, but i only know a way by using a 'general DLL' to
let it work (so i omit that).

Ok, much of words and noise to explain a single line:

Debug.Print Evaluate(Replace(fctString, "theX", "1.3"))

For a german Excel it might look like this:

  Sub simplePrototypSolution()
  Dim fctString, yString, x, separatorTst, xStr, y
  fctString = "-1.4*sqrt(2)*exp(-(theX^2))"
  x = 1.3
  separatorTst = InStr(1, CStr(1.1), ",")
  If 0 < separatorTst Then  ' xlDecimalSeparator ?
    xStr = Replace((CStr(x)), ",", ".")
  End If
  yString = Replace(fctString, "theX", xStr)
  y = Evaluate(yString)
  Debug.Print (fctString)
  Debug.Print (yString)
  Debug.Print (y)
  End Sub


Total Posts: 833
Joined: Jun 2004
Posted: 2005-04-21 21:32

As i like Graeme's approach i combined it with Excel's Evaluate to get a flexible
solution (and added a Gauss Legendre integration). Now integration can almost
be written down as one would have to look it like (except namings).

Attached File:


Total Posts: 3465
Joined: Jun 2004
Posted: 2005-04-22 13:26
Thanks, AVt!
I've pursued Graeme's sugestion as well,
but not nicely as yourself.
Gonna to study your code thoroughly.

BTW, thanks for the 2D integrator!

Нас ебут, а мы крепнем...
Previous Thread :: Next Thread 
Page 1 of 1