Making non-linear FEA with Excel go (much) faster

The spreadsheet presented in the previous post takes about 90 seconds to complete (with the iteration limit set at 500) on my computer, and is much slower on some other systems.  The VBA code that does all the hard work is based on Fortran code published in  Programming the Finite Element Method, 5th ed. John Wiley & Sons, I.M. Smith, D.V. Griffiths and L. Margetts (2014), so I have now linked the spreadsheet input to a modified version of the compiled Fortran code.

Download, including full open source code.

To run the program, copy all the contents of the zip file to the same directory, and it should work.

As can be seen in the screen shot below (click on any image for a full-size view), changing to compiled code has resulted in a huge improvement in run time, reducing the total time to 3 seconds, a factor of 30.


At this stage I have compiled the Fortran code as an exe file, that reads the input data from a text file (generated by Excel), and writes the results to 3 results text files, which are then read and parsed in VBA.  This is not the most efficient way to transfer data to and from compiled programs, but it is the easiest to implement, and the overall effect on the run time is totally negligible.

The next post in this series will look at the VBA code required to generate the input data file, run the exe file, and read the results, and will be followed by the procedure for compiling the Fortran code, using the open source MinGW and GFortran programs.

The spreadsheet input is the same as the previous version, except that elements allocated with each soil property type are now specified in the property table.  The elements are numbered from left to right, and top to bottom, so the input in the screen shot below shows Property Type 1 allocated to the first 200 elements, forming the soil slope, and Property Type 2 to the remaining 150 elements in the foundation layers.  In this example both of the material types have been allocated the same properties, which are the same as in the previous post.  The number of iterations and element deflections are almost identical to those from the VBA code.  The reported residual error and maximum deflection values are calculated differently in the new code, so the reported values are different, but they represent the same deflected state in the soil.


The next screen shot shows the result of increasing the stiffness of the foundation layers by a factor of 10.  Deflections are greatly reduced, but the strength reduction factor at failure is unchanged:


Reducing the foundation layer stiffness by a factor of 10 shows greatly increased deflections, but the reduction factor for failure remains unchanged at 1.6:


In the example below the soil slope has been divided into two layers, with the lower layer having a reduced friction angle, and the foundation layer an increased friction angle.  The strength reduction factor is now reduced to 1.4:


Moving the reduced friction angle to the top layer, the reduction factor for slope failure returns to 1.6:


As in the VBA version, the slope width and height parameters may be adjusted to any value, but the graphic display will not work if the number of elements are changed. This will be fixed in future versions, which will also allow more flexible input of the cross-section shape.

This entry was posted in Computing - general, Excel, Finite Element Analysis, Fortran, Geotechnical Engineering, VBA and tagged , , , , , , , . Bookmark the permalink.

3 Responses to Making non-linear FEA with Excel go (much) faster

  1. Pingback: Running exe files from Excel | Newton Excel Bach, not (just) an Excel Blog

  2. Paris Altidis says:

    The code runs much much faster indeed. A minor issue: in the Deflect Worksheet, Cell A19 refers to some real value when it should have been the label for Node 16.


    • dougaj4 says:

      Thanks for that. Now fixed.
      I had been writing the completion time to cell A19 on scrap sheet during trial runs, and I must have started one from the Deflect sheet instead.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s