Eval.xls and the ALGLIB integration functions.

Posted on 11/09/2010 by dougaj4

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.

Posted in AlgLib, Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , | 4 Comments

The Eden Project

Posted on 06/09/2010 by dougaj4

Continuing the Buckyball theme, The Eden Project in Cornwall, UK, is an old quarry  site converted to an environmental exhibition centre featuring three large dome structures:

More details at the Galinsky site.

Posted in Newton | Tagged , , | Leave a comment

Drawing a Buckyball in Excel

Posted on 04/09/2010 by dougaj4

Since, according to Google, it is the 25th anniversary of the Buckyball, I thought I would draw one in Excel.

A quick search found a list of coordinates here: Chemistry resources

The problem then was, how to connect them.  The information was probably somewhere at the same site as the coordinates, but not in a form I could understand, so I pasted the coordinates into Strand7, and connected the nodes by trial and error, until I got what I wanted.  The list of node connections could then easily be copy and pasted into Excel:

Buckyball coordinates and connections

It was then a simple matter to paste the coordinates and connection details onto my Plotxyz spreadsheet, press the Draw button, and hey presto – one Buckyball:

Buckyball

The plotxyz spreadsheet, including Buckyball details, can be downloaded from PlotBuckyBall.zip

Edit Sunday 9:00 PM Sydney time:

Since it is still just Buckyball day somewhere on the globe I thought I’d share a few more images.

The buckyball consists of 90 edges, 60 long ones (1.453 angstroms) and 30 short ones (1.367 angstroms). These form 12 pentagons and 20 hexagons.  The pentagons have all long edges, and the hexagons have alternating short and long edges.  To make this difference clearer I have coloured the long edges green and the shorter ones red.  In addition, to enhance the 3D effect, I have used a heavier line for the edges facing the viewer.  Here are the results:

Buckyball viewed from a distance

Close up view

Posted in Drawing, Excel, Maths | Tagged , | 10 Comments

Excel, ALGLIB, C# and ExcelDNA

Posted on 01/09/2010 by dougaj4

Having just responded to a comment, saying I would be focussing on Add-in Express, rather than ExcelDNA, I have just found a blog post about using the ALGLIB C# routines in Excel, with the aid of ExcelDNA:

Parcell’s Posts; Tutorial: Numerical Analysis in Excel using C# with ExcelDna and AlgLib

I will be looking at Add-in Express in the near future, but ExcelDNA just got added to the to-do list.

Posted in Excel, Link to dll, Maths, VBA | Tagged , , , | Leave a comment

Route Map …

Posted on 29/08/2010 by dougaj4

… so we know where we are going.

I’ll be starting several, to some extent interlinked, new themes in this blog over the coming weeks. 

  • Firstly VBA Classes.  Regular readers may have noticed that my VBA code tends to be class-free.  The main reasons for that is that I haven’t seen the benefit of class based code for what I have been doing, and when I have looked at implementing classes I have found the documentation confusing and I have gone back to my old ways.  For reasons discussed below I have now decided that the time has come to learn the class based approach seriously, so what I plan to do is implement a “Units” class which will allow “units aware” calculations to be easily implemented in VBA.  This will also be incorporated in the Eval spreadsheet, allowing units aware calculations on the spreadsheet with no VBA coding.
  • One of the main reasons for the new interest in classes is that I will be looking at the use of VB.Net with Excel, and since VB.Net follows the “object-oriented” approach more completely than VBA it makes sense to adopt the use of a class based approach at the same time.  The use of VB.Net will include both writing VBA routines to interface with compiled VB.Net dll routines (as I have previously done for Fortran and C++), and also using Add-in Express to write VB.Net code that works directly with the spreadsheet.
  • On the engineering side I will continue to work on Excel versions of the ALGLIB library and also start a series on using an Application Programming Interface (API) to link Excel to a finite element analysis program.  The program I will be using is Strand7, but the methods used should be applicable to other programs with similar functionality.  Strand7 has recently had a major new release, including a greatly expanded API.  This provides another incentive for adopting the class based approach, since I will be re-writing much of my API library, and the use of class based code should allow this to be done more efficiently.
  • I will also be returning to the Excel frame analysis spreadsheet, and the plotting spreadsheet which have been presented here previously, to incorporate additional functionality and links to compiled routines for better performance.
  • The reinforced concrete analysis spreadsheets will also be further developed, with further developments of the crack width, time related strain, and moment-curvature analyses, and also analysis of differential strain effects and composite construction.
  • Finally, the general science related posts will continue, featuring interesting and entertaining (I hope) applications of some of the Excel analysis tools featured on the blog.

Any other topics you would like to see covered here?   Let me know.

Posted in AlgLib, Concrete, Excel, Finite Element Analysis, Frame Analysis, Link to dll, Maths, Newton, VBA | Tagged , , , , , | 4 Comments