The functions described in recent posts provide iterative solutions to polynomial equations, but the same method can be used for any equations that can be evaluated numerically and have a single variable. The question then is, how can we call a function as a variable, rather than hard-coding the function call in the calling function. The answer lies in the Application.Run method:
An example of this method is given in the file PassFunc.xls, which as usual includes full open source code. The file includes an amended version of the QuadMuller function (and the associated SolvexM function), and a function to evaluate the second moment of area of a segment of a circle about its base, given the radius of the circle and the width of the segment:
The calculations performed by the function CircleIsb are:
The formulae for the segment area properties are taken from the Section Properties spreadsheet (version for Excel 2003 and earlier).
The screenshot below shows an example of the QuadMuller function calling the CircleIsb function to find the width of a circular segment of radius 600 mm with a second moment of area of 5.00E9 mm^4.
If using this method to write your own routines note that the arguments to the called function are passed by value, and hence arrays must be declared as variants, rather than explicitly declared as arrays (which can only be passed by reference). Coincidentally, Tushar Mehta has posted on a similar topic at Daily Dose of Excel and his own site. His post provides a class based approach, and also gives more details of how the application.run method can be used (in effect) to pass data by reference.
You can also use CallByName in VB/A, as long as the method you’re calling is in a class…
If working with VBA / Excel one can try the approach shown in http://axelvogt.de/axalom/Function_as_Arguments_in_Excel.zip,
which uses integration of a function as an example
For the benefit of others Axel’s code references a discussion at:
Which is worth a read.
Pingback: The Inverse Quadratic Method 3 – Brent’s Method « Newton Excel Bach, not (just) an Excel Blog
Pingback: Calling a function as a variable – another example « Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 30: Data Transfer, to and from VBA | Newton Excel Bach, not (just) an Excel Blog