## Numerical integration with on-sheet calculations

Following a comment here I have prepared a spreadsheet that works through the Tanh-Sinh Quadrature process with on-sheet calculations.  For that purpose Graeme Dennes has provided a simplified version of the code:

Translated from HP RPN code written by Cesar Rodriguez.
This is the second fastest T-S program I have found, second only to the program by Michalski and Mosig.
This is also the shortest T-S program I have found!
No subroutines. Two nested Do loops.

The spreadsheet calculations and the new VBA code have been added to the zip file with the full set of integration routines, which can be downloaded from:

The VBA version of the Quad-Rodriguez_Tanh-Sinh function can be used in the same way as the other functions by Graeme, either evaluating functions entered as text on the spreadsheet, or using the built-in VBA functions:

The spreadsheet calculations on the next sheet show the VBA code in Column A, with the corresponding spreadsheet calculations to the right.  The three grey shaded cells allow user input of any function of a single variable and the integration limits:

The bulk of the work is done in two nested do loops, with the outer loop repeated four times, and the inner loop up to 15 times, with the spreadsheet calculations in Columns G to AO in the two screenshots below.  Click on the images for a larger view, or download the spreadsheet to follow the calculations in detail:

The results from each of the four outer loops are shown below, with increasing precision from each loop.  The final loop returns the exact value of pi to 15 significant figures (the greatest precision available in Excel), and agrees exactly with the VBA function results:

Note that if a different function is entered in cell G4 the spreadsheet calculations will be updated, but the “actual error” figures will not be correct, since they assume an integration with a result equal to pi.  Also there may be small differences between the VBA and on-sheet results, since the VBA adds iterations where necessary to reach the required precision, but the on-sheet calculations have a fixed number of iterations.

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

### 10 Responses to Numerical integration with on-sheet calculations

1. Is it just me or is this file corrupted? Is anyone else observing the same behavior?

Like

• dougaj4 says:

Checking the download file, I got an “invalid link” message at start-up. I then opened the previous version (5.0), which opened with no problem, and re-saved the latest version, and it now opens for me without any problem.
I have now uploaded this file to the link at the top of the post. Please let me know if it works for you now.
If you still have a problem please give more details of the problem, and let me know which version of Excel you are using, including 32 bit or 64 bit.

Like

• I can open the first link now, nevertheless, for the second one I get:
The file couldn’t open in Protected View message.

Like

• dougaj4 says:

I don’t know what you mean by first and second link.

If you un-zip that file you should get:
Tanh-Sinh Quadrature v5.01 by Graeme Dennes.xlsm

Are you getting the error message when you try to open the .xlsm file in Excel?

If so, what is the exact error message?
What version of Excel?
What operating system?
32 bit or 64 bit?

Like

• Sorry if I was confusing previously. To clarify, in your first sentence (Following a comment here …) word “here” is a link towards your earlier post where you have ||Tanh_Sinh Quadrature|| link for download. I can open the files from that post.

In particular, I get two messages:
First one – “Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.”.
Once I click ok, I get the second error message: – “The file couldn’t open in protected view”.

I’m using 64 bit version of Microsoft Office Professional plus 2019, =INFO(“RELEASE”)—>16.0, however now that I wrote this I just remembered that I had Office 365, it seems that I haven’t renewed my license. I don’t know whether this could be in connection with the problem. Will check once I renew and get back to you.Thanks in any case for responding and in general for such a useful site.

Both of the links contain a file called:Tanh-Sinh Quadrature v5.01 by Graeme Dennes.xlsm which seems to be the same, which further rises concern that the issue is with me.

Like

2. dougaj4 says:

Thanks for the details Milos.
It’s rather strange because the two links connect to the same file on the server. Perhaps your browser cached a copy from a previous download.
If you still have problems after renewing please let me know.

Like

• Craig says:

I had the exact same issue Doug.

It seems to be related to opening the file directly out of the zip, just couldn’t get it working this way. Extracting it and clicking through the same errors and then enabling protected view and then enabling macros via the prompts seems to get it working for me. Same issue with earlier v5, and new v5.01 version.

Like

3. dougaj4 says: