Building a Structural (Engineering) Design eWorkbook – Part 4

So as mentioned in state-of-play 2018/wk50  I got sidetracked using MSHTML to read data from html files, specifically the Chrome bookmarks file (the exported HTML not the main JSON formatted file). Also I started writing the state-of-play article on the thursday and got interrupted so completed today (19/12/2018).

Now using VBScript doesn’t seem much of a eWorkbook. But as previously indicated it is similar to using the MatLAB like family of products: Octave, Scilab, Freemat and possibly python. The advantage however is it can be readily converted to VBA and operate behind the scenes in an MS Excel workbook or other software which has an integrated VBA editor (VBE).

My understanding is that software like DesignCAD and Multiframe are COM server software whilst Office products are COM server/client software. I have never liked the idea of VBA attached to Word documents or AutoCAD documents. Attaching VBA to MS Access and MS Excel I find acceptable because of two reasons.

  1. Excel worksheet calculations can be enhanced by use of VBA functions and it makes sense to be able to write the functions without resorting to some other software and installing an add-in each time need a function.
  2. Access as a DBMS involves a lot of data validation and manipulation code added to input and presentation forms, and makes sense adding to the database file. On the other hand I prefer the multi file systems of DBASE IV and Paradox 3.5.

Adding VBA code to drawings and word documents on the other hand I see as contaminating the file. A productivity or automation macro doesn’t need to be part of the document file. It shouldn’t be part of a document file because its purpose should be to operate on multiple files and shouldn’t have to duplicate such code in every document, and it’s just weird for it to be centralised in such document (eg. word normal template).

So when I automate AutoCAD/ProgeCAD or Word I do so from MS Excel or MS Access. Typically Excel, because I have done calculations first using Excel then drive CAD, or otherwise simpler to collect random data from word in a worksheet than Access tables. Other examples involve data from MS Access into Word, so Access controls Word, rather than have Word grab data from Access.

To avoid restricting to a single office application it becomes more productive to program in VBscript than VBA. The workbooks I build are heavily dependent on VBA, in particular my TechLIB add-in.

Now over on ExcelCalcs there is a disparity between the downloads of the TechLIB and the workbooks which depend upon. I can therefore only surmise that many of the people have limited skill in using MS Excel. Which is potentially understandable, have more important things to do than fight with computers.

On the other hand the data I need resides in various software packages, and needs to be moved between them. If I cannot automate the transfer of data from one application to another, then I have a bottleneck.

Drafters for example automate drawing details in AutoCAD, but they use some 2 to 5 times more parameters than the engineer uses in design. If the engineers calculations drove drawing the detail then it would be far more efficient and productive. A lot of early engineering software merely crunched numbers without any illustration. When illustrations added, then a simple bitmap which never changes. Newer software generates parametric sketches in the software, but doesn’t export this for inclusion in formal technical drawings. But the software obviously has all the necessary data required to produce such drawing, it just needs to produce in the right format.

So my assumption therefore is that putting M=wL^2/8 into a spreadsheet is child’s play, and M=WL/8, where W=wL, is even easier. Pushing known values through simple or complex formulae is a relatively trivial exercise. The difficult stuff is adding data validation, or conditional testing of which formula to use, and iteration, convergence and solving systems of equations. Things which are cumbersome to setup in worksheets or otherwise simply error prone and far more quality robust if written in VBA than some fudge using spreadsheet cell formula.

The following image illustrates the use of schTechLIB to calculate phi.Ms and phi.Mb to AS4600. It uses functions which make use of DAO to get data from MS Access tables. Without the use of DAO the functions would require passing all the section properties and would have far longer parameter lists.

Using schTechLIB to caculate phiMs and phi.Mb to AS4600
Using schTechLIB for Beam Design Checks For Simple Flat Canopy

With my typical 18 column worksheets the whole sequence can be collapsed into about two lines. One line for the repetitive information, and one line for each segment check, or one or more lines for each separate member.

If I do the calculations in the worksheet then I fill an entire page (A4 sheet) and thats with the calculation of the effective section modulus hidden in VBA. If I use a worksheet to calculate the effective section modulus then fill and entire page, and thats with hiding all the individual calculations to check effective width of each plate element. If check Hancock’s book on AS4600 then will see that the whole calculation sequence can stretch to several pages.

On a day to day basis it is not necessary to see all the intermediary calculations, they serve no purpose unless there appears to be a problem with the result. So as with most things we should have different presentation of the calculations depending on the audience or the specific need at a given point in time.

As noted in earlier post the data file for SMath is an XML file, so if want a more MathCAD type presentation we could use VBA to export the MS Excel worksheet to SMath. alternatively could make use of XLC in a worksheet from excelcalcs. The point is that can easily use MS Excel is a simple calculator, and from there improve the presentation to better match a given purpose.

I have mentioned purposes and types of workbook previously in the following posts:

  1. eWorkBook Challenge: spreadsheets and other calculation workbooks
  2. Ten Types of Spreadsheet or eWorkBook Applications

With more discussion of calculation process and data formats in the following articles: 

  1. On Calculations and Software Part 1
  2. Data file formats for interchanging and sharing data from user applications

Put simply I am starting from VBscript and working towards VBA attached to MS Excel. Unless have need for some specialist libraries there is no need to get on the latest band wagon and adopt python. More over most of the calculations expect to perform, also expect can do with pencil, paper and a pocket calculator. So anything likely useable in a standard library can also write that, and avoid the bloat of stuff do not need.

For structural design I need to do simple arithmetic calculations to various national standards, for this purpose I have schTechLIB.xlam, an Excel add-in. Product specific (shed, canopies, balustrades) workbooks are then built using the library. The workbooks change the presentation but the calculations to the standards remain the same.


  1. [19/12/2018] : Original