Composite Beam Spreadsheet 3

The Composite Beam Analysis spreadsheet, previously discussed at: has now been updated with a VBA User Defined Function (UDF) in place of the previous macro that used the Excel Goal-Seek command.  This has two main advantages:

  • The UDF is much faster than repeated use of the Goal-Seek command.
  • The UDF is much more flexible, for instance allowing the analysis to be performed at a series of sections along a beam, with the load information in tabular format.

The new spreadsheet may be downloaded from Composite Design Functions .  As usual, the download file includes full open source code.

Not that the previous version is still available from Composite Design

A screen-shot of the input sheet for the new function is shown below:

Input for Composite Design Functions spreadsheet, click for full size view

The basis of the new UDF is the QuadBrent function, previously presented at  This function uses Brent’s Method (an elaboration of Newton’s Method) to solve equations by iteration.  In this case a two stage iteration was required:

  • The main ElasticComp function calls the QuadBrent function with “FindCurve” as the target function.  FindCurve adjusts the section curvature with a constant Neutral Axis depth so that the resultant bending moment on the section is equal to the applied load.
  • For each iteration of the FindCurve function  the QuadBrent function is called again with “EvalForceMom” as the target function.  This function adjusts the top-face strain, with constant curvature so that the resultant axial force on the section is equal to the applied load.
  • This process is repeated until both bending moment and axial force are equal to the applied loads, within the specified tolerance.

The other main change from the previous version is that the load data may now be entered in a single row, and that the Stage 2 loading may be enetered as 2 or more separate parts, so that if the beam is modelled as two separate elements with a rigid connection in a finite element program, for instance, the loads on the two elements may be entered seperately, and the program will combine them for the analysis.  Examples of alternative layouts of equivalent applied loads are shown below:

Alternative Load Layouts

This entry was posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

5 Responses to Composite Beam Spreadsheet 3

  1. Pingback: Daily Download 2: SLS design of reinforced concrete sections … | Newton Excel Bach, not (just) an Excel Blog

  2. Stephen says:

    Hi Doug,
    I’m using the VBA/UDF spreadsheet exactly as downloaded including section properties and loads, except that I’ve changed the array formulas starting in cells L9 & M9 of the output sheet to refer to L7/M7 instead of blank cells L24/M24.

    Could you please explain what results are being shown by cells L9:L11 and M9:M11. If I calculate eg an ‘implied area’ of super-tee flange reinf = (M9*1000/L9) =1417, but the actual area of the super-tee flange reinf is 2011 (10-N16). The bottom prestressing areas also don’t tally.

    Also, the stress reported by cell L10 is 6.5 MPa. I assume this is for the upper layer of prestressing. I’d expect this to be 63.6 MPa by proportioning from the bottom layer stress of 69.7 MPa.


  3. Pingback: Composite Beam Spreadsheet Update 1.01 | Newton Excel Bach, not (just) an Excel Blog

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