The Composite Beam Analysis spreadsheet, previously discussed at: https://newtonexcelbach.wordpress.com/2012/04/27/composite-beam-spreadsheet-2/ 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.

Not that the previous version is still available from Composite Design Functions-GS.zip

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 https://newtonexcelbach.wordpress.com/2010/04/13/the-inverse-quadratic-method-3-brents-method/.  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:

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. 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 sq.mm, but the actual area of the super-tee flange reinf is 2011 sq.mm (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.

Like

• dougaj4 says:

Yes, there is something not right there.
http://www.interactiveds.com.au/software/Beam%20Design%20Functions.zip
and enter the same super-T section and loads, it gives exactly the same results in output columns 1 to 4 (except it lists from compression face to tension face, rather than top to bottom), but columns 6 and 7 are different. For the steel stresses it gives:
69.7, 63.6, 0.401
and Forces:
98.733, 90.071, 0.665

Based, on Force/Stress x 1000,the top steel area is 1658.8 mm2, but that’s the effective area, allowing for displaced concrete. Dividing by (1- 35/200) (1 – Econc/Esteel) gives 2010.6 mm2.

Thanks for pointing that out. I’ll update the composite beam spreadsheet as soon as I have time.

Like

• dougaj4 says:

This site uses Akismet to reduce spam. Learn how your comment data is processed.