Need a book? Engineering books recommendations...

Return to index: [Subject] [Thread] [Date] [Author]

RE: Calculations

[Subject Prev][Subject Next][Thread Prev][Thread Next]
 I tend to prefer Excel/VBA combination. When ever I get a desire for
Mathematical type and look at the MathCAD examples I always reach the same
conclusion: presentation of far too much data to reach the conclusion.

Most code requirements are dependent on large number of conditional
requirements to select the appropraite formula to use. I find VBA code
easier to read, than Excel cell formulae, and I don't see the need to
present the various options in the printed calculations which tends to be
the approach with MathCAD. I prefer hiding certain detail. Manually it would
represent the difference between that presented in the main body of the
report versus, that in the appendices, versus that on scrap paper kept for

For example in coldformed steel design calculation of effective section
modulus can take several pages if all calculations were presented, and it
needs calculating for each set of different conditions for phi.Mb. To
automate the calculations in an Excel worksheet requires a circular
reference to force iteration, and circular references are typically flagged
as errors. By hiding the calculations away in VBA, the iteration procedure
is more readable, and the worksheet just as a single cell formula, instead
of several pages. Also when it comes to hot rolled design, most design is
carried out using design capacity tables (DCT's), so simply having a value
for phi.Mb with out the detail calculations is not typically a problem. My
view is that the independent technical expert reviewing the design is meant
to check the specification not my calculations nor my arithmetic, but rather
conduct independent calculations. The codes of practice provide the formula
and procedures, and all have reference numbers.

It is the calculations not in the codes which need more disclosure. But once
again here most hand written calculations do not present any algebraic
formula or reference as to what is being calculated, just expressions with
numbers, the person reviewing meant to understand and work out the
procedure: or toss aside and conduct independent calculations.

Calculations are a means of reaching decisions. In the main however, we are
not conducting the calculations for such purpose, but to provide a record
that some assessment was made: the design decision already made, for it is
simply a repeat of the past. To me a lot of the calculations are a waste. A
design-curve could be plotted, on this curve between any two points is an
infinite number of other points: people seem to be spending entire careers
calculting those other points, when the design-curve gives us the results we
need, and a few points only are required to plot the curve.

It all depends on the purpose of the calculations. With VBA can grab data
from one program, pull into excel transform and write out for use by another
program. With Excel can also build larger and larger models, which reference
a common parameter list. With Excel can develop simple calculators, which
just give the results, which can then be hand written into reports. For
example I print moment diagrams from frame analysis software, then hand
write the phi.Mb values obtained from Excel calculators, I don't print Excel
reports out.

With VBA functions I also have a choice between calling the functions in the
worksheet cells, or writing a larger VBA application which writes results to
the Worksheet. Basically writing an application in say Delphi or C++,
requires a lot of effort to get data into the program, edit the data, and to
produce and format a report, and also storing and retrieving data from a
file. With Excel and MathCAD these aspects of the application are

I tend to spend as much time in the VBA editor of Excel as I do working in
the worksheets. I find Excel a useful all round general purpose data
processing tool, though for large amounts of data MS Access is better,
though the most of the VBA code is reusable across applications.

My first priority is to reach the design decisions quickly, by automating as
much as possible. So wind loads for example calculated in Excel used to
automatically generate an input file for say MicroStran or Multiframe. But
it depends on the extent of the repetitiion that your projects present.

If have a high level of repetition then getting the resulst quickly and a
compact report are desirable. If really novel and unique, then a more formal
and complete presentation of all calculations becomes more important. With
pencil and paper typically being the fastest way to find the solution: once
found then it could be written up in MathCAD or Excel (using XLC) for
presentation and experimentation with changes of parameter. 

Choice therefore depends on the purpose of the calculations. Also I tended
to prefer Quattro Pro spreadsheet, but working on contract, most places had
Excel, so for that reason and the benefit of VBA I changed over to Excel.
Plus IT managers tend to oppose running *.exe applications that they have
not checked, but otherwise permit running VBA macros. So whilst wouldn't let
me run Turbo C programs for generating ACAD scripts (*.scr), would allow me
to run Excel/VBA macros. MathCAD, TEDDS etc, are not that readily available,
the libraries and templates are also short on Australian Codes. Whilst MS
Office tends to come preloaded on most computers. So making the best of the
tools readily available tends to be my first choice rather than acquiring
additional tools. For example I do everything I can to avoid using
Multiframe, the need to use puts a bottleneck in the whole exercise. If can
do everything in Excel then have a high level of productivity. Basicaly
Excel can do all the calculations in less than a minute, double the number
of calculations and still less than a minute. The major time consumer is
adjusting the input parameters. So that for simple repetive type structures
can complete the entire calculations in less than 5 minutes. Using out of
the box tools, it takes longer because have to step through individual
tasks, rather than process the entire structure from a few simple
parameters. But does require that using Excel with the intent of
identifying, separating out and listing the parameters which define the
system. Rather than running through a ritual of producing calculations.

If want the presentation then MathCAD or Excel with XLC add-in (from
ExcelCalcs) is the way to go. If want diagrams to illustrate the
calculations, then TEDDS  or similar software templates is the option to
choose. If the calculations are just a component of the over all task then
Excel/VBA is my first choice. Central VBA library also reduces inconsistency
between multiple worksbooks conducting similar calculations.

What I do however does have a lot of repetition, and I work for
manufacturers and builders who want standard designs. So I am more
interested in maximum capabilities (design-curves and other compact
presentations) rather than one-off point-value custom calculations.

The choice really comes down to a matter of personal preference. Some people
type their presentation calculations up in MS Word. Masterseries PowerPAD
structural software is also built around the use of WORD rather than Excel.

Conrad Harrison
B.Tech (mfg & mech), MIIE, gradTIEAust
South Australia

******* ****** ******* ******** ******* ******* ******* ***
*   Read list FAQ at:
*   This email was sent to you via Structural Engineers 
*   Association of Southern California (SEAOSC) server. To 
*   subscribe (no fee) or UnSubscribe, please go to:
*   Questions to seaint-ad(--nospam--at) Remember, any email you 
*   send to the list is public domain and may be re-posted 
*   without your permission. Make sure you visit our web 
*   site at: 
******* ****** ****** ****** ******* ****** ****** ********