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