As indicated in previous post I’ve been considering whether to move away from spreadsheets for my calculations. I have previously discussed the use of MathCAD and similar software versus the use of spreadsheets on my personal blog in the post: Electronic Calculations (eCalc’s) . In the previous article my focus was protesting against those who consider MathCAD is the only proper software to use all the time: rather than selecting tools appropriate to the task. MathCAD’s strength is presentation, not getting the job done, not putting numbers to use to control machines. Most people do calculations as “a means to an end” not as “an end in itself”. MathCAD favours those whose purpose is calculations as an in itself. MathCAD is also expensive, and it doesn’t provide me with any benefit to justify the cost. A gratis variant of MathCAD is SMath Studio. Since it can present the calculations, I will start with that. This time not looking at presentation of an involved mathematical expression, as may encounter in cold-formed steel design, but rather a simple sequence of calculations. The calculation task is to get from a wind pressure coefficient to the maximum bending moment in a beam. Actually I would probably start with a pocket calculator and push some numbers through it. So here is the results of using ATCalc on a computer.

ATCalc as a results panel allowing the calculation to be done sequentially. The results could be copy/pasted to a text editor and extra information added to make more meaningful. ATCalc does support variables, though its a bit cumbersome to use. It also supports a Pascal like programming environment.

Another simple calculator is Calc98, it was my preferred desktop calculator before I found ATCalc. Calc98 supports reverse polish notation (RPN), my preferred mode for a calculator, however with out a proper calculator keyboard and relying on the mouse it is cumbersome to use. If it would work on my android phone that would be good, though I currently have HP21 simulator installed. As can be seen in the image of Calc98, only see the results, not the input expression: but since the calculation sequence is in RPN, probably wouldn’t want to see it.

So the calculators give me the numbers, and with ATCalc can easily edit the input numbers and recalculate: but no documentation of what the input numbers and results represent. That is where SMath comes in. Using SMath I can use variables and assign either values or expressions to them, and present almost similar to how I would write by hand. If writing by hand the expression for ‘M’ would have a more slanted line than horizontal, and units would be alongside each expression. The advocates for MathCAD push its abilities to workout units. I did some checking and it seems SMath supports units.

However whilst SMath supports units, its derived units are not the appropriate units to use. Whilst energy, work done (force x distance) has the same units as turning moment (force x distance), it is not usual to refer to a moment using the units of energy. Moment is normally given the units Newton metres (Nm), if I start with kiloNewton’s for force then I also expect the result to be in kiloNewton’s, so expected derived units to be kNm, not J. However can see how SMath has converted the units, and how the numerical result is a 1000 times bigger than expect, as a consequence. So seems like it is preferable to ignore its unit conversion feature, and just write units as text. Anycase, here I am only using SMath to present the calculation sequence. Note in the second screenshot I introduced an extra variable, ‘s’. Note that units are not derived or presented for algebraic expressions, nor are intermediate results displayed.

The next common tool for calculations is MatLAB, for matrix laboratory. Like MathCAD, MatLAB is expensive, free variations are Octave, SciLab and FreeMat. Thus far my preference has been for the FreeMat interface, though Octave seems to be generally considered the most compatible with MatLAB, I don’t use MatLAB so that doesn’t concern me. Whilst SciLab has some more advanced features. Using freemat I can type the calculations directly into the console and get the results of each step echoed to the screen. After which the results are lost unless save to a transcript file: such file as with the console display, is untidy, far too many blank lines. For reuse of the calculation, so that can edit the input values, the calculation sequence can be saved in a script file, as follows:

Cpe=-0.7

qz=0.96 %kPa

pn=Cpe*qz %kPa

w=pn*3 %kN/m

L=6 %m

M=w*L^2/8 %kNm

When ran in the FreeMat console the results of each calculation is echoed to the console, but the expressions themselves are not seen. So unlike SMath we are getting calculated results not a presentation of calculations, to understand the calculations need to read the script.

These days MatLAB is being displaced by the use of the python programming language. As with FreeMat I could type the sequence of commands directly into the interpreter, or save the sequence as a script file so that can easily be edited for alternative numbers. Thus far not considering writing user interface, to get values for parameters, data storage or results storage. Just concerned with calculating results. The python script is:

Cpe=-0.7

qz=0.96 #kPa

pn=Cpe*qz #kPa

w=pn*3 #kN/m

L=6 #m

M=w*L**2/8 #kNm

print(‘Moment: %.2f’%(M))

Note the inclusion of the ‘print’ statement in the python script. When the python script is run, it doesn’t echo anything to the console, so to get some result out need to include a print statement.

Note that converting from FreeMat to python is a relatively simple exercise, and even simpler if I didn’t include the comments identifying the units of measurement. Also note that in neither language have variables been declared or types been specified.

Considering that Windows comes with Windows Scripting Host (WSH) which by default can run scripts written in either VBScript or JScript. Don’t really need to download any bulky package to automate such simple calculations. So here is the VBScript version of the program, with option explicit off by not being included.

Cpe=-0.7

qz=0.96 ‘kPa

pn=Cpe*qz ‘kPa

w=pn*3 ‘kN/m

L=6 ‘m

M=w*L^2/8 ‘kNm

Wscript.Echo “Moment: “, FormatNumber(M,2)

Once again need a print statement to get some output from the program. The following shows the script as run in Vbsedit, with result shown in output window.

Alternatively the script can be run in a command prompt window.

Or if prefer the JScript variant of language for WHS, here is the script:

Cpe=-0.7;

qz=0.96; //kPa

pn=Cpe*qz; //kPa

w=pn*3.000; //kN/m

L=6.000; //m

M=w*Math.pow(L,2)/8; //kNm

WScript.Echo(“Moment: ” + M.toFixed(2));

JScript is a dialect of JavaScript. Both VBscript and JScript versions of the calculations could be placed in a web page and enhanced with web forms and reports using html. Note that one irritation with JavaScript is the use of the dot notation to reference the Math library and the lack of a symbol for exponentiation.

So another option would be to write the code in FreeBasic, as follows:

#include once “string.bi” ‘required for format function

Dim Cpe as Double

Dim qz as Double

Dim pn as Double

Dim w as Double

Dim L as Double

Dim M as DoubleCpe=-0.7

qz=0.96 ‘kPa

pn=Cpe*qz ‘kPa

w=pn*3 ‘kN/m

L=6 ‘m

M=w*L^2/8 ‘kNm

Print “Moment: “, Format(M,”0.00”)

The FreeBasic script is longer, because option explicit in on by default, and requires that both variables are declared and types specified. FreeBasic is not a scripting language, but compiled and produces an ‘exe’ file, which can be distributed without the source code. Staying with dialects of Basic, the following script is using LibreOffice Basic (LoB) in LibreOffice Calc.

Sub Main Cpe = -0.7 qz = 0.96 'kPa pn = Cpe * qz 'kPa w = pn * 3 'kN/m L = 6 'm M = w * L ^ 2 / 8 'kNm Msgbox "Moment: " & Format(M,"0.00") End Sub

Compared to FreeBasic the code has shrunk as don’t need to declare the variables. Compared to MS Excel/VBA editor however, there is no immediate window, and have to trace the program using popup message boxes. Also compared to all the scripting languages it is now necessary to place the code in a subroutine. Without an immediate window,LibreOffice CAlc is not as convenient as the other environments for programming. Here is the code in Excel/VBA:

Sub MainApplicationV1() Cpe = -0.7 qz = 0.96 'kPa pn = Cpe * qz 'kPa w = pn * 3 'kN/m L = 6 'm M = w * L ^ 2 / 8 'kNm Debug.Print "Moment: ", FormatNumber(M, 2) End Sub

The VBA code is mostly similar to VBscript and can largely be used unmodified. The modifications are largely concerned with the development environment not the language. My preference however is to have option explicit on and declare variables and types, so the script would look like the following:

Sub MainApplicationV2() Dim Cpe As Double Dim qz As Double Dim pn As Double Dim w As Double Dim L As Double Dim M As Double Cpe = -0.7 qz = 0.96 'kPa pn = Cpe * qz 'kPa w = pn * 3 'kN/m L = 6 'm M = w * L ^ 2 / 8 'kNm Debug.Print "Moment: ", FormatNumber(M, 2) End Sub

When run from the immediate window using the VBA editor (VBE) the result will print to the immediate window. Thus the MS Excel vbe environment isn’t much different than the console environments of FreeMat and python.

However, when the scripts are run they do not document the calculation process the way that SMath does. On the other hand LibreOffice Calc and MS Excel provide the worksheet environment as well as the programming/scripting environment. So here is the calculation sequence in LibreOffice Calc:

Using Calc as a quick calculator, I have the variables, the results of intermediate calculations, and the units identified. It is in a format, that can quickly use the names on the left to assign range names to the cells on the right. The cell calculations can then use the range names instead of the cell addresses. Similarly using MS Excel:

Note in the input bar, the calculation is shown using cell addresses. The spreadsheet presentations can be improved by adding more text and formatting.

However whilst the algebraic expressions are now shown, they can be different than the calculations actually taking place in the cells, hence the preference by many for the use of MathCAD/SMath. Note that the cell formulae are now using range names and more recognisable than cell addresses. And still other variants:

Using MS Excel or other spreadsheet, I can simply type the expression I input into ATCalc into a single cell and get the answer sought, or I can present the sequence of calculations with description. Alternatively I can go into the VBA editor and write the calculation sequence as a script. Rather than the script as a subroutine I can write it as a function, and then call the function in a worksheet.

Option Explicit Function getBendingMoment(Cpe As Double, qz As Double, s As Double, L As Double) As Double Dim pn As Double Dim w As Double pn = Cpe * qz 'kPa w = pn * s getBendingMoment = w * L ^ 2 / 8 'kNm End Function Sub MainApplicationV3() Dim Cpe As Double Dim qz As Double Dim s As Double Dim L As Double Dim M As Double Cpe = -0.7 qz = 0.96 'kPa s = 3 'm L = 6 'm M = getBendingMoment(Cpe, qz, s, L) Debug.Print "Moment: " & Format(M, "0.00") & " kNm" End Sub

An example of a simple reference to function in a worksheet:

Note the use of named ranges, and the call to the user defined function. If press the ‘fx’ button then get the following dialogue box to input parameter values.

Whilst there are workbook versions of python available, such tools tend to be only available online, and not otherwise suitable for offline use. SMath has the benefit of presentation linked to calculation , whilst FreeMat provides the features for workings with matrices.

Whilst an engineering education may involve lots of advanced mathematics, using calculus, vectors, matrices and complex numbers, most engineering calculations in practice are relatively simple sequence of algebraic expressions . Expressions are seldom as involved as that presented in my earlier article. Most involved mathematical expressions are a result of inexperience. There is only any point substituting one expression into another, if, it results in a simplified expression which improves understanding the relationship between variables. Otherwise it is preferable to retain the smaller sequential expressions: as the stepwise calculation aids understanding, and each step provides insight, allowing the calculation sequence to be halted if it is not heading in the right direction, it also reduces errors evaluating the result.

Compare the calculation at the start of this article, I just input the known values directly into a calculator, tracing an error or recognising the calculation is in error is not so obvious. In the SMath presentation I simply included the 3m load width of the beams in the calculation of the uniformly distributed load. It is not so obvious that is the purpose of the value 3, so in some of the other presentations I included an extra variable ‘s’. It is also to be noted that all the scripting languages support functions, and can be written similar to the VBA example.

It is however the spreadsheet environments that provide the simplest means of providing a user friendly means of providing input values, and presenting reports. Though FreeMat and SMath may provide the easiest means of plotting charts from mathematical expressions without need to generate a data table. In practice however, more likely to start the process with a raw data table or values calculated at incremental points: so the plotting capabilities of FreeMat/SMath not overly a benefit.

For example I have spreadsheets which calculate and plot moment diagrams along the span of a beam. The table is necessary because cannot add point loads and uniformly distributed loads, but can add the bending moments. So the beam is broken into segments, typically 10 to 50 segments, and moments are then calculated for each load type, and then added, to get the moment at a point. The maximum moment can then be found from the sequence of points, no chart is required, but it helps. The cell calculations use VBA functions to get the bending moments. However, using a worksheet is not the most efficient process, as it is a hassle to convert the worksheet from 10 data points to 50 data points. If did the calculations in VBA it would be easy to change from 10 data points to any other number of points, including for example 1000 data points. The more segments the better the result.

As can be seen from the various spreadsheet presentations above, once a calculation is set up, it isn’t the end of the exercise. In the above simple calculation sequence, the value of ‘Cpe’ needs to be calculated somewhere else, as does ‘qz’, and the load width or spacing of the beams can also be calculated elsewhere, and likewise the beam span may also be the result of additional calculations. Thus additional calculations can be inserted before the ones presented, likewise the results need to be incorporated into further calculations to check or determine a suitable structural section to use for the beam. The simple little spreadsheet thus becomes, heritage and foundation on which to build more comprehensive spreadsheets.

However worksheets are limited in what they can do with calculated results. the worksheet for example cannot produce a drawing in Acad. A spreadsheet application such as MS Excel or LibreOffice Calc can control other applications or even devices via the use of VBA/LoB. Data in a worksheet can be used to generate a drawing in Acad or a structural model in Multiframe: it just requires time to create such automation tools.

At present the LoB development environment is not as convenient as VBE, and Excel itself also has some usage features more convenient than LibreOffice Calc. At first glance the centralised macro library in LoB seemed a benefit over VBA, however it is not as centralised and accessible as first appears. So at present my preference is MS Excel over LibreOffice Calc. Followed by a preference for VBA functions over complex cell formulae.

By adopting Excel/VBA for calculations, it is possible to transform VBA code into VBScript for use where scripts are more suitable. For example I have converted our plane frame application to a console application using VBScript. I also have many VBScripts for sorting files, which use the string handling functions extracted from VBA. Whilst code for working with regular expressions which started in VBScript has been converted to VBA. Similarly the VBA and VBScript code for plane frame analysis converted to FreeBasic and VB.net. The VB.net version then extended from a console application to a windows desktop application, with graphics of the moment diagrams etc…

So unless need advanced numerical methods there is no need to move over to SMath, FreeMat or python. Even then, if only need some relatively simple methods, it is probably more useful to write them in VBA than to move over to some other environment with the methods built into available libraries. Libraries are fine but often times contain more bloat than benefit, they also impose their approach to the problem. For example my bending moment function as presented above is dependent on wind loading, such function would be more useful if it simply calculated the bending moment: M=wL^2/8. But even there still have options, for example many industry manuals present the expression as M=WL/8 where W=wL, this makes it more comparable with moment for a point load which can be presented as M=PL/4 or M=WL/4, where W=P. then again may not want the maximum moment may want the moment at a point along the beam, in which case the expression is slightly more involved. By creating VBA functions I don’t have to remember the formula, nor go hunting for my reference books, or get scrap paper and work it out. Solve it once and then make use of the solution.

My conclusion is stick with whatever calculation tool currently using, if it is working for you. My quest is moving from worksheet calculations back to more programmatic calculations, as I did way back when, in the beginning using Turbo Pascal and Turbo C. That is less interest in presenting calculations and more interest in using the calculations to drive other tasks. *{After all my education does cover PLC’s, CNC machine code, factory automation and robotics, DAQ/DAC. Sizing beams is relatively dull by comparison. Now throwing building of an assembly line that’s interesting.}*

Revisions:

- [03/12/2017] : Original