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.
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 Functions-GS.zip
A screen-shot of the input sheet for the new function is shown below:
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: