Using Technical Library and Database for Cold-Formed Steel Design to AS4600

As I mentioned in Building a Structural (Engineering) Design eWorkbook – Part 4  a calculation of section and member moment capacity to AS4600 can be relatively simple, with my original illustration of such being presented here: On Calculations and Software Part 1.

Simple worksheet to calculate phi.Ms and phi.Mb to AS4600

Using such technical library , and materials library, it is possible to generate simple member capacity tables and then from the table generate design curves.

Member Capacity Table for Cold-Formed C-Sections to AS4600
Member Capacity Curves for Series of Cold-Formed C-Sections
A More Detailed View of Member Capacity Curves for Small Cold-Formed C-Sections

Another use is comparing calculation of member capacity using Zc based on critical stress f*, compared to Ze based on fy. The curves show it is typically conservative to use Ze, which is the expected out come. So for practical design of cold-formed sections more complex than c-section, just need some more advanced software to calculate Ze. Once have Ze, it can be stored in database along side Zf, and spreadsheet can then be used to complete rest of calculations to AS4600.

Calculations to AS4600 comparing Calculation of phi.Mb based on Ze vs Zc
phi.Mb calculated using Ze (yellow) and Zc (blue)

All that is required is to store the MS Access database file in an appropriate folder, and then install and activate schTechLIB.xla or schTechLIB.xlam depending on version of MS Excel choose to use.

If need to check a cold-formed steel beam in another worksheet, say set up to design sheds or carports, there is no need to use the quick calculator spreadsheet included in: Spreadsheet Bundle for Cold-formed Steel Structures Design. However if got the quick calculator working then the technical library add-in should be working, and therefore can refer to the library functions in own worksheet. The quick calculator worksheet just provides an example of the function usage: and is useful when using MicroStran or Multiframe to analyse a frame and need to check member capacities, and don’t have AS4600 design modules.

Setting up the spreadsheet environment to use the Technical library and material database is explained in the following post: Setting Up Spreadsheet Environment and also in the following pdf document: SpreadsheetEnvironmentIssue1.pdf.

Once the library is installed can then press the “fx” button in MS Excel worksheet (its to the left of the edit bar). It will bring up dialogue allowing selection of functions: choose user defined then scroll the list to see functions available.

User Defined Functions Available to MS Excel WorkSheet

When a function is inserted this way also get a dialogue box for selection of the input parameters.

Input Parameters Required by User Defined Function

The function library is more useful than worksheet calculations as the VBA can be added to other applications such as MS Access, or otherwise converted to other high level languages with relative ease and used in more complex applications.

Product Configurators

The shed and carport industry for example, really need systems built around database management systems such as MS Access rather than being built around MS Excel. The reason is that the main issue is managing orders, accumulating sales history and forecasting future requirements. The main need is materials requirements planning (MRP I) and manufacturing resource planning (MRP II), and most likely enterprise resource planning (ERP) systems.

Whilst shed/carport manufacturers are typically seeking structural spreadsheets it isn’t what they really need. They need custom product configurators attached to DBMS, driven by structural calculations.

Worksheets are fine for presentation of reports and as data input forms, but cumbersome for multiuser database management systems. When someone in the factory earmarks some cold-formed steel coil for a shed, it shouldn’t get used to supply someone else’s roof purlins. The MRP/ERP systems help manage the resources. A product configurator is just one interface to such systems: and it is preferable that the calculations behind such configurator are written in high level programming language than thrown together using  worksheet formulae. More so if the database is MySQL or some other server based database.


  1. [19/12/2018] : Original
  2. [20/12/2018] : Added comparison of Ze and Zc.