Since last September Graeme Dennes has continued work on his Tanh-Sinh Quadrature spreadsheet, and has recently released Version 4.0, which can be downloaded from: Tanh-Sinh. As usual, the download includes full open-source code and extensive documentation.

The new version includes routines to conveniently evaluate the integrals of functions coded in VBA, as well as those entered on the worksheet. This approach provides much better performance, as described by Graeme below:

Much, MuchFaster Tanh-Sinh Quadrature v4.0 by Graeme DennesTo the best of the author’s knowledge, this V4.0 package remains the fastest, most powerful, most accurate and most comprehensive general-purpose quadrature package available today at no cost. It includes full open source code and extensive documentation.

Yes, this V4.0 release is

much, muchfaster than the V3.0 release due to a fundamental change in the method used to evaluate the functions being integrated. This is the main, exciting, change for this version of the workbook.For previous releases, and which is the default option in this release, the functions to be integrated are stored in worksheet cells, and evaluated by the quadrature programs as required. The cell-stored method may continue to be used in the identical manner as in previous releases.

Doug Jenkins (privately) raised the concept of the functions being stored internally as VBA program statements, being called by the quadrature programs when required. Based on initial code provided by Doug, the author has implemented the VBA-stored method as an option for all 13 quadrature programs. Each program worksheet has been provided with a control button to demonstrate either the VBA-based results or cell-based results, allowing for an immediate comparison of the execution times between these two function storage methods.

Why go to all this trouble? Consider the improvement afforded to the Tanh-Sinh program. With all 200+ test functions held as VBA-stored code, the total worksheet calculation time shows a dramatic speed increase of

twenty-seven timesover the cell-stored method!!! This is an unprecedented out-of-the-real-world improvement to the performance of the Tanh-Sinh program. (The other programs also show performance improvements of varying degrees.) Refer to the VBA TESTS worksheet for the indicative speed comparisons between the two storage methods for the 13 quadrature programs. All several hundred test functions used in the 13 program worksheets are also included as VBA-stored functions, allowing for easy speed comparisons between the two storage methods. If you thought the Tanh-Sinh program’s performance was excellent in the past, wait until you experience the performance when using VBA-stored functions. Hang on to your hat!!

Caveat:Implementing the functions internally as VBA program statements requires VBA programming skills. The top of the modulem_EvalFunccontains the basic instructions for entering the functions as VBA code statements.Doug also provided an example of a custom function which in turn calls a quadrature program to integrate a VBA-stored function. This greatly increases the speed of the custom function. It also improves the overall utility of this workbook, in that it provides the basis for having a library of regularly used custom functions which are also able to make use of the new storage method, while at the same time perform other associated calculations (if required) in conjunction with calls to integration programs, etc. Iterative programs, such as these 13 quadrature programs, maximise the benefits of the VBA storage method because the functions being integrated may need to be evaluated many times for each solution, maximising the time-saving provided by this method. Refer to the last two examples on the T-S EXAMPLES worksheet for two custom functions demonstrating the method. The code for the included custom functions is located at the end of the module

m_EvalFunc.In summary, the huge speed improvement achieved through using VBA-stored functions is

immediatelyobvious and worthwhile. We do live in exciting times! Thank you Doug.Also in this release, the speed of the Gauss-Kronrod program has been significantly improved through the inclusion of the Epsilon algorithm of Wynn to extrapolate the limit of the series, speeding up convergence. This has allowed the order of the Gauss-Kronrod program to be changed from 20/41 to 10/21. These two changes have improved the performance by a very nice 40 percent.

Graeme Dennes

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

Pingback: what Microsoft think VBA is good for … | Newton Excel Bach, not (just) an Excel Blog

it would be really helpful if a routine ‘ ready to be integrated in other programs ‘ could be available. All I want is to be able to use this as a module that I call in another VBA code

LikeLike

Thank you for your interest in the Tanh-Sinh Quadrature workbook. The latest version is V4.1, which may be downloaded from here:

https://newtonexcelbach.wordpress.com/2013/03/16/tanh-sinh-quadrature-v4-1/

At the bottom of the “T-S EXAMPLES” worksheet are two examples which describe and demonstrate how to call two custom functions – LTED and CIRCPARABSB. These two custom functions reside in the last 100 or so lines of the VBA code module m_EvalFunc, and make calls to the Tanh-Sinh program.

Study the included text and the method used by the two custom functions to call the Tanh-Sinh program with the necessary parameters, hopefully enabling you to make similar calls to the Tanh-Sinh program from your own VBA code. The first Tanh-Sinh calling parameter is the analytic function to be integrated, which in the present workbook, resides in the EvalFunc UDF as a VBA-stored function.

Graeme Dennes

LikeLike

Thanks, will try

LikeLike

In addition to Graeme’s comments, the link below might be useful:

https://newtonexcelbach.wordpress.com/2013/08/21/running-vba-routines-from-a-new-workbook/

LikeLike