Building a Structural (Engineering) Design eWorkbook – Part 1


Actually from my viewpoint not engineering because:

Engineering takes place at the frontiers of science and technology. It is a high risk activity, based on highly deficient knowledge, with uncertain outcomes.

The thing we are concerned with here are established technologies, with an established body of technical knowledge, making planning, design and management based on near complete knowledge with highly certain outcomes. For this activity I prefer to use the word technical rather than engineering. So technical design rather than engineering design.

Since we are dealing with technical design, we have an established body of technical science and technical mathematics which we can apply to assess the fitness-for-function or suitability-of-purpose for parametric variations of the established generic technologies. Buildings, bridges and machines structures are in the main established technologies: more than one has been built, more than one is in operation, more than one has been decommissioned and more importantly most of the technical science is more than 50 years old. If one of these structures fail, we do not expect an investigation to lead to the discovery of some new scientific phenomenon.

With an established body of technical science we have the opportunity to automate the calculations and the design process, which is otherwise a bottleneck to getting the technologies implemented. When the need for a house, hospital or school is recognised, it is typically required yesterday not one year from now. So it would be preferable if could get from a building supermarket, that is order today and have installed and fully operational tomorrow. Not an overly realistic expectation, but an expectation nonetheless. Therefore anything which delays such objective is unwarranted bottleneck to be removed.

So structural calculations determine dimensions, and dimensions can control machine  tools. The future is likely to see more use of additive 3D printing in manufacturing than material removal processes. Material removal processes waste materials and energy. For example if calculation suggest use of a 24mm diameter shaft, then it is preferable to adopt the stock standard 25mm diameter shaft and use the stronger shaft than expand energy machine down to 24mm. That is using energy to reduce strength is a waste. Product design calculations need to merge into process control calculations.

The more complete the calculation model, potentially the better the decision. When I first started using the computer, especially spreadsheets the idea was to reduce time taken for calculations and increase consistency of the calculations, and to improve the detail of the calculations from project to project. By detail I don’t mean express and present all background calculations, I don’t really care if calculation process is hidden, what I care about is that the calculation process is extensive and as complete as possible. When I started my view was that the assessment of bolted moment connections used in cold-formed steel shed design was deficient and relatively poor.  By using spreadsheets I could do the existing calculations that were being submitted in significantly less time, thus providing more time to concentrate on connection design, and so the spreadsheets can become more rigorous in the assessment of the structure, and take less time to do so. So basically the spreadsheets allowed me do do more in less time, and improve from project to project.

However as spreadsheets become more complex it takes more time to read the spreadsheet. For example my balustrade spreadsheets I can probably work through in about 15 minutes, if I am checking structural adequacy of balustrades daily, and those balustrades exactly match the spreadsheet. If this extends to weekly, or a year or more passes, then it would take about 2 hours to work through the spreadsheet and check what it is doing is valid for a current project. Typically each project involves different base connections and these typically take upto around 2 hours to create a new worksheet to assess: sometimes it can take a week  depending on the suitability of the support structure. So that which could be a 15 minute project becomes a minimum of a 4 hour project. Similar goes for sheds and canopies (carports and verandahs).

Design effort is not proportional to the size it is proportional to the complexity. However complexity has a tendency to increase with size. So has long as size increases without increase in complexity then the design effort for a 3m span garden shed is the same as that for a 21m industrial shed. For those consultants that work on a percentage fee, the 3m shed won’t cover the labour costs of design. It is important that the 3m shed is designed once and built many times to cover the cost of design. Those consultants who run out of multi-million dollar projects and move onto the small scale have unrealistic expectations of project worth and grossly unrealistic views on their importance. It is important to understand that consultants are typically an unwanted bottleneck, an hindrance to the whole process, imposed on builders/makers by legislation. It is important to demonstrate completeness and speed (eg. development application should be approved without any request for further information: established technology therefore known or at least knowable).

So task is to fulfill the logistical requirements of:

  1. Right goods
  2. Right time
  3. Right place
  4. Right condition (quality)
  5. Right quantity

That is need to enable and empower those people who are in need of technology to gain access and use of such technology as quickly as possible to meet their needs. How do we get this “engineering” thingy to people in remote rural communities? As far as most people are concerned these “engineer” things just do the numbers, and in the main the majority are only capable of doing the numbers. Bad news: a brainless unimaginative block of silicon can do the numbers. A design model can be created once, a product configurator can be created once, and then it enables many people to custom design their desired product. An “engineer” needs to be able to do more than just the numbers. So I reiterate: Engineering takes place at the frontiers of science and technology. It isn’t about doing the numbers and most especially not the numbers for parametric variations of established technology.

So we are not about to create a eWorkbook which can do engineering, nor can it do design. All the computer program can do is crunch numbers, generate drawings and control machine tools.

Here our task is automating valid specification based on a technical assessment of suitability-of-purpose based on technical science: that is design guided by a rational scientific process. Cumbersome statement hence: “engineering”.

Getting Started

So not doing engineering, mostly crunching numbers to guide decision making, the decisions then become written specifications and/or drawings. I don’t have access to machine tools, so not covering that part.

First of all, I am not going to cover how to use Excel/vba, there are plenty of websites providing guidance for such, my prefered sites are:

  1. Excel Macro Mastery
  2. Pearson Software Consulting
  3. ExcelCalcs
  4. Peltier Tech Blog
  5. JKP Application Development Services

There are others, however my focus is using a combination of Excel and VBA. If Excel didn’t have vba then I would have stayed with Borland Quattro Pro, it was discovering that Excel 97 had vba, and that vba was also used across Office 97 and so could be used with MS Word and MS Access that I changed. Prior to the change I had to learn different macro languages for Paradox (along with DBase/Foxbase), Wordperfect and Quattro Pro. With vba the code is reusable across all office applications, and can also control each of the other applications from any one of the applications. My preference is to control all from MS Excel. Additionally I use MS Excel to control DesignCAD, IntelliCAD and Multiframe, whilst other software benefits from generation of data files such as Microstran .arc files.

Also since I already have spreadsheets to meet most of my structural design needs I am not going to cover structural design, rather I am going to start with my spreadsheet bundle. Few people have provided feedback on ExcelCalcs, most feedback comes from students/developers who have requested the spreadsheets directly, which is part reason for bundling the spreadsheets and distributing from this site. The download count on ExcelCalcs indicates many people do not understand how to use Excel Add-in’s as there is a mismatch between the download of the add-in and the workbooks which depend on.

The spreadsheets in the bundle are cut down versions of those I use: they are cut down so that users can assemble workbooks to best suit their needs. I have spreadsheets with different presentations, there are basically three forms:

  1. Calculation Report for Submission for Development Approval
  2. Quick Calculator (desktop)
  3. Quick Calculator (slim view for use with android google sheets )

My calculation reports seldom present detail formula, for example I will provide value for a property such as “Fo” from AS4600 without showing the formula. It is an intermediate property and how I got it is largely irrelevant, all that really matters is the value of phi.Ms not how I got it. If were using AS4100 then could simply write phi.Ms down from the AISC/ASI design capacity tables. Not everyone has those tables so could question the validity of such presentation, however it is the independent reviewers task and responsibility to use alternative tools to check and validate proposals. So they can use limsteel or their own spreadsheet if they so wish.

So I don’t need to present detail. If I use Multiframe or Microstran for structural analysis, then I tend to use the AS4600 quick calculator and write the values of phi.Ms on the printouts of moment diagrams, with some indicator of additional inputs (eg. Lx=Ly=Lz). So for many situations a quick calculator is all that is needed, and if it works on a smartphone then even better.

Noting that my spreadsheets started in Quattro Pro (QPro) and then converted to MS Excel 97/vba, I am mostly going to concentrate on the problems encountered over the years, and improvements in the vba languages and its library. For example in QPro I made extensive use of dialog boxes, in moving to Excel 97, using dialog boxes seemed far more involved, and instead opted for using data validation on cells in the worksheet. So worksheets which were data stores in QPro became input forms in Excel.

Also originally I named start cells and used offset extensively rather than using cells to increment from one cell to another. However there are lots of benefits to be had by naming blocks.

I make extensive use of linked workbooks, but this can cause problems, on the other hand repeating code across workbooks has other problems. Similarly I make use of data access objects (DAO) to get structural section properties directly from an MS Access database rather than using VLookup worksheet functions.

Whilst many things can be done in a worksheet, sometimes such approaches are cumbersome, and whilst an interesting challenge to achieve, it isn’t very efficient nor are such solutions quality robust. VBA is typically the better approach for involved calculations involving many conditional tests.

For example I have a spreadsheet for calculating effective section modulus (Ze) of a c-section using iterative capability of spreadsheets. However the whole calculation spreads across an entire A4 sheet, and the spreadsheet flags a circle reference error: such circular reference is required to force iteration. Using VBA and DAO can get the value of Ze calculated in a single cell: much more efficient when checking several structural sections in one workbook.

A workbook can be used to design/check a single element of a structure or an entire structure. My original eWorkbooks had one task per workbook, such as loading to AS1170, cold-formed steel to AS1538/AS4600, and steel to AS1250/AS4100. But then I joined several of these to design sheds and carports.

My original shed spreadsheet in QPro could easily be changed from gable roof to skillion roof, and from shed to canopy. This flexibility came from it writing a data file for our inhouse plane frame analysis program. Once I added Kleinlogel calculations to Excel97/Excel2003 the workbook became locked to gable shed design.

As I indicated in a recent post I have written wrapper functions for AS4100 so that like AS4600 it makes use of DAO to get data. Better still would be able to have a single function which can easily swap between AS4600, AS4100, AS1720 (timber) and AS1664 (Aluminium).

Similarly I have been working on single function which gets pressure coefficients for buildings and canopies.

So the posts I am going to write in this series will mostly cover deficiencies in the approaches adopted and also in the alternative approaches: that is what are the benefits and disadvantages of various approaches, when using Excel or other spreadsheets for structural assessment.

So the areas of structural design to be covered are:

  1. Dimension and Geometry
  2. Design Actions
  3. Design Action-Effects
  4. Member Resistance
  5. Connection Resistance
  6. Footing Resistance

Structural analysis is the one task which is least suitable for calculation directly in a worksheet. Structural analysis is best carried out using external software such as Multiframe via COM automation, or Microstran via generation or .arc files. Though simple beams and columns can be assessed in spreadsheets, and so can simple plane frames which can be analysed using plane frame formula such as those found in Kleinlogel or Roark’s. Setting up such formula however can be time consuming and it may not be productive unless have frequent use of such formula: that is it is faster to build a model in general purpose structural analysis software than setup formula from scratch.

Our inhouse plane frame analysis is available in multiple programming languages and as an Excel Add-in. I have already provided examples of alternative front-end and back-end applications for such analysis engine. However these examples have just been concerned with using Excel to get data into and out of the program, they haven’t demonstrated the reason for using Excel in the first place: namely calculating the dimension and geometry along with the design-actions: I assumed that was obvious.

So in the next post I will discuss data requirements, where to store this data (eg. why I use MS Access tables), the structure of the data, and separating:

  1. data collection
  2. editing
  3. calculation
  4. reporting


  1. [08/07/2018] : Original