Building a Structural (Engineering) Design eWorkbook – Part 5

So in part 3 I made use of various DBMS to store input data and calculated results. the data stored in tables, each row of the table is a record and each column of each record is a field. High level programming languages allow data structures to be defined collecting all the fields together into a single entity, allowing blocks of data to be passed around using a single variable instead of long lists of variables. In Pascal these data structures are called records, in C they are struct’s and in VBA they are user defined types (UDT). Though declaring VBA supports UDT’s is a bit misleading as the record type is the only UDT supported, which is very limited compared to the user defined types permitted by Pascal and C. Anycase my preference is to stick to calling them record types. In VBA such record type is defined by a TYPE … END TYPE statement.

For example in the technical library there is the a record type called TStructSECTION which is used to collect data from the materials database. Its definition is as follows:

Public Type TStructSECTION
descr As String 'Section Name
Fabgroup As String 'Fabrication Group
Type1 As String 'Type Classification
swt As Double 'Self weight
d As Double 'Depth of Section
df As Double
b As Double 'Breadth of Section
b2 As Double 'Breadth of Bottom Flange
dL As Double 'Depth of Flange Lip
Tf As Double 'Thickness of Flange
tw As Double 'Thickness of Web
ri As Double 'fillet radius
Area As Double 'Area of x-section
Ix As Double 'Second Moment of Area XX axis
Zx As Double 'Section Modulus
Sx As Double 'Plastic Section Modulus
rx As Double 'Radius of Gyration XX axis
xbar As Double 'Centroid x coord
xo As Double 'shear centre x coord
Iy As Double 'Second Moment of Area YY axis
Zy As Double 'Section Modulus
Sy As Double 'Plastic Section Modulus
ry As Double 'Radius of Gyration YY axis
ybar As Double 'Centroid x coord
yo As Double 'shear centre y coord
j As Double 'Torsion Constant
Iw As Double 'Warping Constant
Qf As Double
Beta_x As Double
Beta_y As Double
C_Y As Double
ZX_MIN As Double
ZY_MIN As Double
SectionType As Integer '{1=C-SECTION,2=CHANNEL,3=CLOSED RHS/SHS}
BuiltUpType As Integer '{0=as is,1=boxed,2=I-beam,3=tucked,4=other}
Fy As Double
fyf As Double
fyw As Double
fu As Double
ClosedSection As Boolean
DataSource As String
End Type

This record is not used on its own but as part of another record, the TStructMember record used for collecting information about structural members together.

Public Type TStructMEMBER
Qty As Double 'Number of Members
QtyR As Integer 'Number of Restraints to Member
Lc As Double 'Centreline Length of Member
Le As Double 'External Surface Length of Member
Li As Double 'Internal Surface Length of Member
Lap As Double 'Length of Lap if any
Spacing As Double 'Centre to Centre Spacing of Members
Sect As TStructSECTION 'Section Properties of Member
Mtrl As TStructMATERIAL 'Material Properties
End Type

VBscript however doesn’t support record types it does however support the class data structure, which is similar but with added facility. The class version in VBscript would be as follows:

Class TStructMEMBER
Dim Qty 'Number of Members
Dim QtyR 'Number of Restraints to Member
Dim Lc 'Centreline Length of Member
Dim Le 'External Surface Length of Member
Dim Li 'Internal Surface Length of Member
Dim Lap 'Length of Lap if any
Dim Spacing 'Centre to Centre Spacing of Members
Public Sect 'Section Properties of Member
Public Mtrl 'Material Properties
End Class

Note that the types haven’t been given because VBscript doesn’t support declaring types. In VBA the class is defined in a class module and looks like:

Option Explicit
Public key As Integer
Public Name As String
Public Qty As Double 'Number of Members
Public QtyR As Integer 'Number of Restraints to Member
Public Lc As Double 'Centreline Length of Member
Public Le As Double 'External Surface Length of Member
Public Li As Double 'Internal Surface Length of Member
Public Lap As Double 'Length of Lap if any
Public Spacing As Double 'Centre to Centre Spacing of Members
Public Sect As TStructSECTION 'Section Properties of Member

The benefit of a class over a record is that the class also contains functions and subroutines which work with the fields. There is no need to pass parameters to the functions because the class already is aware of the parameters. Additionally a field can be replaced by a function so instead of calculating a value and storing it in the record, the field is always up to date because it always recalculate based on current value of other fields. With the use of properties a field can also be made read/write or read only or write only. As I learnt object oriented programming (OOP) using Turbo Pascal, I typically prefer declaring public variables rather than using VBA property let/set/get statements. These special property statements tend to require additional variables: one variable name visible to the user of the class and the real variable behind the scenes, for simple variables I consider this to be a waste. 

As an example in TBuildStruc I have a property AverageHeight, this calculated from the eaves height and ridge height. The height to the eaves is an input parameter but the height to the ridge is calculated.

Property Get AverageHeight() As Double
AverageHeight = (HeightEaves + HeightRidge) / 2
End Property

The average height and height to ridge are both read only properties, they are calculated from other data fields defining a building structure. It could equally well be defined using a function statement, rather than property get statement. If average height wasn’t calculated then would need to get value from some other variable say xAverageHeight.

Option Explicit
Class clsBuildingStructure
Dim xAverageHeight

property let AverageHeight(xAvgHeight)

  xAverageHeight = xAvgHeight
  end property

  property get AverageHeight
AverageHeight = xAverageHeight
  end property
End Class

Dim simpleBuilding
Dim HalfHeight
Set simpleBuilding = new clsBuildingStructure
simpleBuilding.AverageHeight = 7.8
HalfHeight = simpleBuilding.AverageHeight/2
WScript.Echo simpleBuilding.AverageHeight,HalfHeight

Note that with the property statements, it just adds code and no real value compared to simply having a public field called AverageHeight. If only let/set property defined then field is assign/write only, if only get property defined then read only.

Now whilst the classes can reduce the number of parameters which need to be passed around functions and subroutines, they are not compatible with MS Excel worksheets. A function to be used in a worksheet has to have simple string or number parameters, the only real exception is a range object. A bunch or parameters can be passed to a function by passing a block of cells. For that to be useful the cells need to be in the correct sequence or the cells contain variable names and values and the data has too be parsed to assign the rights values to the right internal variables.

So classes best suit calculations inside VBA. So if the parameter list for a function is getting too long for a worksheet function, then probably time to start doing the calculations completely in VBA, or otherwise simplify the function.

Having defined a class I tend to give all classes the following subroutines:

  1. Initialise:  to set all fields to default values
  2. sprint: to combine fields into a single string
  3. cprint: to display field values using debug.print (or wscript.echo)
  4. fprint: to write the fields to a file
  5. wbkprint: to write fields to a MS Excel workbook

Most of the names are based on functions in C programming language, since using VBA not C, there is no keyword clash. Alternatively could replace print with report. 

So record types currently used in the technical library will be converted to classes. Many of which actually started as classes in Delphi, and were converted to record types because Excel 97/VBA did not appear to support classes in an appropriate manner: as I wasn’t going to write property gets and lets statements for each public field, and dream up extra variable names.

VBA now supports public fields in classes. Converting the records to classes  and moving the functions and subroutines into the class modules, allows the object to be removed from the parameter list. Secondly it allows much of the dot notation to be removed, as the subroutines work directly on the fields of the class, rather than on the fields of some object based on the class.

For example in the simple class defined above the halfheight could be defined as part of the class and then would simply be halfheight=AverageHeight/2, as its part of the class definition it doesn’t need and object reference.

One disadvantage is that the class module can get fairly large, and similar to a program with global variables. Thus subroutines which were split between multiple modules or files have to be combined into the one class module/file. (Though from memory I believe allows a class to be split between multiple files) 

As the class gets bigger and gets more variables and more subroutines, it starts becoming questionable where a variable gets its value from. It’s a matter of subjective judgement as to whether a variable should be in the parameter list of a subroutine/function or a field variable of the class.

Whilst a class should be kept simple and do one thing well, it’s not that simple if the language doesn’t support inheritance. Which is another part of the loss when converted from Delphi to VBA. Inheritance as it was in Turbo Pascal allowed the properties of one class to be merged into the definition of another class.  Without inheritance then need a reference object to access the members of the dependent class.

For example in TStructMEMBER there are reference variables sect, and Mtrl, one is of type TStructSECTION, and the other TStructMATERIAL. This is not obvious as VBscript doesn’t declare types. In the VBA example Mtrl is missing because it has been moved to the TStructSECTION class. With inheritance, TStructSECTION could inherit properties of
TStructMATERIAL, and TStructMEMBER could inherit properties of TStructSECTION, and there would be no need for the fields sect and Mtrl.

Anycase these record types and classes are used behind the scenes of the workbooks, especially workbooks like the Design Engine. Which illustrates two different input forms:

Main Input Form to DesignEngine, with Various Options Available
Main Input Form to EngineLT, no Options Available it Just Determines Frame Size

So whilst there may only be one way (not quite) to check the structural adequacy of a cold-formed steel shed frame there is more than one way to get the input parameters and more than one way to present the results. The EngineLT form is quick and simple, and the output could be completely different such as a material order list with correct member sizes or technical drawings with material schedules and correct member sizes.

The size of the EngineLT form also is representative of my early prototyping applications for handheld computers, which have now largely been replaced by smartphones. As another example there is the MS Excel version of barrier post design and a version of the program. There are different versions of the Excel version. Without wind loading the calculations are done entirely in worksheet cells, with wind loading vba functions are used. Ultimately the worksheet just becomes an input form and all calculations are in vba. The vba then translated to and a new input form created. A menu could be added to provide access to extra functionality as also have vba code which generates height versus span tables for balustrades: and writes the results to MS Excel worksheets.

Which then raises issues of interfaces. If I need the MS Excel worksheet for presentation of tabular data, is it better to write the application in Excel/VBA or write it as a stand alone application? In the first instance it maybe easier and faster to write using Excel/VBA, but whether it should stay there depends on the application and end-users: it isn’t about whether they have MS Excel or not. The issue is about whether the user interface of MS Excel be modified to suit the application, or better to leave MS Excel interface alone and create a cleaner purpose made interface for the task at hand? 

Also I didn’t set out to write VBA macro to generate height versus span tables for aluminium balustrades, I was simply working through the required calculations in a worksheet, hit a point where I couldn’t get an answer I wanted using the worksheet, so I wrote a VBA macro for the calculation, which largely replicated the worksheet calculations. Once written it wasn’t that difficult to wrap in a loop and generate height versus span tables. So the worksheet calculations and the VBA code become a check one against the other.

In short the VBA code is more flexible for adapting to other more automated tasks than worksheet calculations. I cannot put worksheet calculations in a loop, I cannot automate an investigation into the effect of changing parameter values. Such things have to be done manually in a worksheet, unless the calculation sequence is simple enough to carry out on a single row, and then copy to multiple rows.

I guess perspective is important. Having spent a lot of time on tolerances and variation, and statistical process control, it never seemed right that we just do a single point value calculation in mechanics. Secondly need to be able to manufacture things: design once and make many times. Running numbers through a standard parametric model of a product, multiple times with pencil and paper, maybe acceptable when pencil and paper is the only option. To do the same thing with a computer is a waste. Manufacturing wise just want to know the limitations of the materials, components and product form.

For example the cold-formed shed industry typically has a C35030 as the maximum section it can use. Is the largest shed which can be made from this section larger than anything anyone would practically want? If we change the structural form can we make bigger sheds from the same section? Do we need a bigger section, is a section larger than C35030 practical?

How can the structural calculations be integrated into the bigger picture?


  1. [20/12/2018]: Original