Calling a function as a variable

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: 

Click for full size view


 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: 

CircIsb Function (click for full size)


The calculations performed by the function CircleIsb are: 

Second Moment of Area of a circle segment about its base


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. 

QuadMuller function used with CircleIsb function


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 method can be used (in effect) to pass data by reference.

This entry was posted in Arrays, Excel, Maths, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

6 Responses to Calling a function as a variable

  1. Jon says:

    You can also use CallByName in VB/A, as long as the method you’re calling is in a class…


  2. Axel Vogt says:

    If working with VBA / Excel one can try the approach shown in,
    which uses integration of a function as an example


  3. dougaj4 says:

    Thanks Axel.

    For the benefit of others Axel’s code references a discussion at:

    Which is worth a read.


  4. Pingback: The Inverse Quadratic Method 3 – Brent’s Method « Newton Excel Bach, not (just) an Excel Blog

  5. Pingback: Calling a function as a variable – another example « Newton Excel Bach, not (just) an Excel Blog

  6. Pingback: Daily Download 30: Data Transfer, to and from VBA | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.