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:

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