Eval.xls and the ALGLIB integration functions.

In a comment on a previous post on numerical integration I mentioned that I might implement the ALGLIB integration functions using the same technique, that is using the VBA Evaluate function to evaluate functions entered as text in the spreadsheet.  Well here they are.  The spreadsheet provides both an efficient high precision integration technique, and a good example of the use of the VBA Evaluate function. 

The ALGLIB functions use the Gauss-Kronrod method, which is a variant of the Gaussian method used in previous versions of this spreadsheet.  The advantage of the Gauss-Kronrod method is that the integration points from previous iterations are re-used in successive iterations, thus saving a significant number of time consuming evaluations.  The spreadsheet with full open source code can be downloaded from Eval.zip

Input and results for the new functions are shown in the screen shots below:

ALGLIB Integration function documentation

Example 1

Example 2

Example 3

Note that the function links to 3 different ALGLIB functions; one for smooth functions, one for smooth functions with narrow bumps, and one for functions with singularities at either end of the integration range.  A later post will examine these options in more detail.

One of the problems with using the VBA versions of the current ALGLIB functions is locating the modules required to run any particular function.  The modules required in this case are listed below:

  • ablas.bas
  • ablasf.bas
  • ap.bas
  • autogk.bas
  • blas.bas
  • creflections.bas
  • evd.bas
  • gammafunc.bas
  • gkq.bas
  • gq.bas
  • hblas.bas
  • hsschur.bas
  • ortfac.bas
  • reflections.bas
  • rotations.bas
  • sblas.bas
  • tsort.bas

The key part of the code is shown below:

' Replace the integration variable with the X value returned
' by the FState function
  EStep = Replace(Func, IntA(1, 1), FState.X)
' Evaluate the resulting expression
  FState.F = Evaluate(EStep)
' Return to ALGLIB iteration function
  ItState = AutoGKIteration(FState)
  Loop While ItState = True
' When ItState is false retrieve the results
  Call AutoGKResults(FState, V, Rep)

Finally, for those looking for efficient numerical integration techniques in Excel, watch out for an upcoming post which will cover a lesser known technique than the Gauss-Kronrod method that gives still greater efficiency for the same level of precision.

This entry was posted in AlgLib, Excel, Maths, Newton, Numerical integration, UDFs, VBA and tagged , , , , . Bookmark the permalink.

4 Responses to Eval.xls and the ALGLIB integration functions.

  1. Pingback: Faster Integration with the Tanh-Sinh Method | Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Return of Excel – Evaluating commas | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Daily Download 17: Numerical Integration | Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: Evaluating text – update | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

You are commenting using your Twitter 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.