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