Building a Structural (Engineering) Design eWorkbook – Part 6

In part 5 I briefly covered classes, whilst in part 3 I covered the use of DBMS to store and retrieve data. One approach for the tables was a table with a record structure: characteristic, value, units, isParameter. For which purpose I wrote a function getCharacteristic and subroutine setCharacteristic to get and set the data in the data tables. With data being grabbed from the tables assigned to ordinary variables and then used in calculations and the results from ordinary variables then stored back in the tables. With such approach the calculations look reasonable with the cumbersome subroutines limited to data input/output from the database.

But what happens if use records or classes for the simple calculation sequence. A possible general purpose record (VBA) is as follows:

Type Structure3D
Cpe As Double
qz As Double
s As Double
L As Double
pn As Double
w As Double
M As Double
End Type

The the calculation sequence can then be determined as follows (VBA):

Function getBendingMomentV2(ByRef struct1 As Structure3D) As Double

With struct1
.pn = .Cpe * .qz     'kPa
.w = .pn * .s        'kN/m
getBendingMomentV2 = .w * .L ^ 2 / 8 'kNm
End With

End Function

Sub MainApplicationV4()
Dim struct1 As Structure3D

With struct1
.Cpe = -0.7
.qz = 0.96
.s = 3
.L = 6
.M = getBendingMomentV2(struct1)

Debug.Print "Cpe = " & CStr(.Cpe)
Debug.Print "qz = " & CStr(.qz) & " kPa"
Debug.Print "s = " & CStr(.s) & " m"
Debug.Print "L = " & CStr(.L) & " m"
Debug.Print "pn = " & CStr(.pn) & " kPa"
Debug.Print "w = " & CStr(.w) & " kN/m"

Debug.Print "M = " & FormatNumber(.M, 2) & " kNm"
Debug.Print "------------------------------------"
End With

End Sub

Using dot notation the formulae have some semblance to familiar expressions, though the dot is slightly distracting. As mentioned in previous posts, VBscript doesn’t support Type..End Type, nor type assignment in the variable declarations, and instead have to use classes, and the code is slightly different.

Option Explicit

'Vbscript Doesn't support Type...End Type
'So implement record type as a class
Class Structure3D
Public Cpe
Public qz
Public s
Public L
Public pn
Public w
Public M
End Class

Function getBendingMomentV2(ByRef struct1)

With struct1
.pn = .Cpe * .qz     'kPa
.w = .pn * .s        'kN/m
getBendingMomentV2 = .w * .L ^ 2 / 8 'kNm
End With

End Function

Sub MainApplicationV4()
Dim struct1

Set struct1 = New Structure3D

With struct1
.Cpe = -0.7
.qz = 0.96
.s = 3
.L = 6
.M = getBendingMomentV2(struct1)

WScript.Echo "Cpe = " & CStr(.Cpe)
WScript.Echo "qz = " & CStr(.qz) & " kPa"
WScript.Echo "s = " & CStr(.s) & " m"
WScript.Echo "L = " & CStr(.L) & " m"
WScript.Echo "pn = " & CStr(.pn) & " kPa"
WScript.Echo "w = " & CStr(.w) & " kN/m"

WScript.Echo "M = " & FormatNumber(.M, 2) & " kNm"
WScript.Echo "------------------------------------"
End With

End Sub

The main difference is the need to create an instance of class, before can start using the object variable. But if using classes can also add functions and subroutines as part of the definition, and by doing the calculations inside the class can avoid the dot notation. So the extended class definition is:

Option Explicit

Class clsStructure3D
Public Cpe
Public qz
Public s
Public L

Dim report(7)

Sub initialise()
Cpe = 0
qz = 0
s = 0
L = 0
End Sub

Sub SetParameters(Cpe1, qz1, s1, L1)
Cpe = Cpe1
qz = qz1
s = s1
L = L1
End Sub

Function calcNetPressure()
calcNetPressure = Cpe * qz
End Function

Function calcUDL()
calcUDL = calcNetPressure * s
End Function

Function calcMoment()
calcMoment = calcUDL * L ^ 2 / 8
End Function

Sub sprint()
report(0) = "Cpe = " & CStr(Cpe)
report(1) = "qz = " & CStr(qz) & " kPa"
report(2) = "s = " & CStr(s) & " m"
report(3) = "L = " & CStr(L) & " m"
report(4) = "pn = " & FormatNumber(calcNetPressure, 2) & " kPa"
report(5) = "w = " & FormatNumber(calcUDL, 2) & " kN/m"
report(6) = "M = " & FormatNumber(calcMoment, 2) & " kNm"
report(7) = "------------------------------------"
End Sub

Sub cprint()
Dim s

sprint

For Each s In report
WScript.Echo s
Next

End Sub

Sub fprint(fpText)
Dim s

sprint

For Each s In report
fpText.WriteLine s
Next

End Sub

Sub cprint1()
WScript.Echo "Cpe = " & CStr(Cpe)
WScript.Echo "qz = " & CStr(qz) & " kPa"
WScript.Echo "s = " & CStr(s) & " m"
WScript.Echo "L = " & CStr(L) & " m"
WScript.Echo "pn = " & FormatNumber(calcNetPressure, 2) & " kPa"
WScript.Echo "w = " & FormatNumber(calcUDL, 2) & " kN/m"
WScript.Echo "M = " & FormatNumber(calcMoment, 2) & " kNm"
WScript.Echo "------------------------------------"
End Sub

Sub fprint1(fpText)
fpText.WriteLine "Cpe = " & CStr(Cpe)
fpText.WriteLine "qz = " & CStr(qz) & " kPa"
fpText.WriteLine "s = " & CStr(s) & " m"
fpText.WriteLine "L = " & CStr(L) & " m"
fpText.WriteLine "pn = " & FormatNumber(calcNetPressure, 2) & " kPa"
fpText.WriteLine "w = " & FormatNumber(calcUDL, 2) & " kN/m"
fpText.WriteLine "M = " & FormatNumber(calcMoment, 2) & " kNm"
fpText.WriteLine "------------------------------------"
End Sub
End Class

With the new class, I have removed the calculated variables (pn,w,M) and replaced them by functions. This way the values are calculated and up to date based on the current values of the parameters (Cpe,qz,s,L). The main program then reduces to:

Option Explicit

Sub MainApplicationV5()
Dim struct1

Set struct1 = New clsStructure3d

With struct1
.initialise
Call .SetParameters(-0.7, 0.96, 3, 6)
.cprint
End With

End Sub

The new program hides the sequence of calculations and doesn’t appear to have anything to do with structures: which may or may not be an issue for some. But now can also place the calculations in a simple loop, say varying the beam span:

Option Explicit

Sub MainApplicationV6()
Dim struct1(2)
Dim i
Dim k

For i = 0 To UBound(struct1)
k = i + 1
Set struct1(i) = New clsStructure3d
With struct1(i)
.initialise
Call .SetParameters(-0.7, 0.96, 3, 3 * k)
.cprint
End With
Next

End Sub

Now aht happens if adopt the characteristic concept used for the data storage in the DBMS. So define a new class: PhysicalCharacteristic.

class PhysicalCharacteristic
Public key
Public Name
Public Value
Public Units
Public formatStr

Sub initialise()
key = 0
Name = ""
Value = 0
Units = ""
formatStr = "0.00"
End Sub

Sub setCharacteristicKeyed(xkey,xName , xValue, xUnits , xFormatStr)

key = xkey
Name = xName
Value = xValue
Units = xUnits
formatStr = xFormatStr

End Sub

Sub setCharacteristic(xName , xValue, xUnits, xFormatStr )

Name = xName
Value = xValue
Units = xUnits
formatStr = xFormatStr

End Sub

Sub setCharacteristicDefinition(xName , xUnits, xFormatStr)

Name = xName
Units = xUnits
formatStr = xFormatStr

End Sub

Function sprint()
sprint = Name & ": " & FormatNumber(Value, formatStr) & " " & Units
End Function

Sub cprint()
WScript.Echo sprint
End Sub
End Class

There are no individual property get/set/let statements, instead using public fields/variables. There a few different subroutines/methods to set the values of a characteristic, set all fields or just set the minimum number of fields. Note that the code was originally written in VBA using the format() function, which is not supported by VBscript. So in VBA would pass a formatStr=”0.00″ to format(), whilst in VBscript passing a number to FormatNumber(). I haven’t renamed the variable to reflect such change as it is of minor importance in over all scheme of things: VBA is where ultimately want the code, VBscript is a compromise for the task at hand.

Using this class, and an array to hold each variable, the sequence of calculations now becomes:

Option Explicit

Sub MainApplicationV7()
Dim struct1(6)
Dim i

For i = 0 To UBound(struct1)
Set struct1(i) = New PhysicalCharacteristic
struct1(i).initialise
Next

'Input Parameters
Call struct1(0).setCharacteristic("Cpe", -0.7, "", 1)
Call struct1(1).setCharacteristic("qz", 0.96, "kPa", 2)
Call struct1(2).setCharacteristic("s", 3, "m", 2)
Call struct1(3).setCharacteristic("L", 6, "m", 2)

'Results
Call struct1(4).setCharacteristicDefinition("pn", "kPa", 2)
Call struct1(5).setCharacteristicDefinition("w", "kN/m", 2)
Call struct1(6).setCharacteristicDefinition("M", "kNm",2)

struct1(4).Value = struct1(0).Value * struct1(1).Value
struct1(5).Value = struct1(4).Value * struct1(2).Value
struct1(6).Value = struct1(5).Value * struct1(3).Value ^ 2 / 8

'Report
For i = 0 To UBound(struct1)
With struct1(i)
.cprint
End With
Next

End Sub

Using the arrays now lost reference to the variables, and the calculations cease to be familiar. Further cannot reference the array elements using the variable names, but could do so if used a collection or dictionary. But first using without arrays, define an additional function to create the instance of the object variable and initialise, so that don’t have to repeat such code multiple times to set up each variable. The script then becomes:

Function setStructuralCharacteristic(xName, xUnits, xFormatStr)
Dim tmpStruct

Set tmpStruct = New PhysicalCharacteristic
With tmpStruct
.initialise
Call .setCharacteristicDefinition(xName, xUnits, xFormatStr)
End With

Set setStructuralCharacteristic = tmpStruct

End Function

Sub MainApplicationV8()
Dim Cpe
Dim qz
Dim s
Dim L

Dim pn
Dim w
Dim M

'Input Parameters
Set Cpe = setStructuralCharacteristic("Cpe", "", 1)
Set qz = setStructuralCharacteristic("qz", "kPa", 2)
Set s = setStructuralCharacteristic("s", "m", 2)
Set L = setStructuralCharacteristic("L", "m", 2)

Cpe.Value = -0.7
qz.Value = 0.96
s.Value = 3
L.Value = 6

'Results
Set pn = setStructuralCharacteristic("pn", "kPa", 2)
Set w = setStructuralCharacteristic("w", "kN/m", 2)
Set M = setStructuralCharacteristic("M", "kNm", 2)

pn.Value = Cpe.Value * qz.Value
w.Value = pn.Value * s.Value
M.Value = w.Value * L.Value ^ 2 / 8

'Report
Cpe.cprint
qz.cprint
s.cprint
L.cprint
pn.cprint
w.cprint
M.cprint

End Sub

With this approach can reference each variable by name, but have to use dot notation to get the variables value, also to print results have to list each variable in sequence. So consider using a dictionary to store the variables and allow looping through to print final report.

Option Explicit

Sub MainApplicationV9()
Dim Cpe
Dim qz
Dim s
Dim L

Dim pn
Dim w
Dim M

Dim key
Dim tmpStruct

Dim struct01

Set struct01 = CreateObject("Scripting.Dictionary")

'Input Parameters
Set Cpe = setStructuralCharacteristic("Cpe", "", 1)
Set qz = setStructuralCharacteristic("qz", "kPa", 2)
Set s = setStructuralCharacteristic("s", "m", 2)
Set L = setStructuralCharacteristic("L", "m", 2)

Cpe.Value = -0.7
qz.Value = 0.96
s.Value = 3
L.Value = 6

'Results
Set pn = setStructuralCharacteristic("pn", "kPa", 2)
Set w = setStructuralCharacteristic("w", "kN/m", 2)
Set M = setStructuralCharacteristic("M", "kNm", 2)

pn.Value = Cpe.Value * qz.Value
w.Value = pn.Value * s.Value
M.Value = w.Value * L.Value ^ 2 / 8

'Report
For Each key In struct01.Keys
Set tmpStruct = struct01.Item(key)
tmpStruct.cprint
Next

WScript.Echo "--------------"
struct01.Item("M").cprint
struct01("M").cprint

End Sub

The dictionary allows accessing the variables by name, rather than by index number. But still got the dot value clutter. As with the earlier class could create functions, however they don’t belong to the general nature of the characteristic class, so leave the definitions outside the class definition.

Option Explicit

Function calcNetPressure(Cpe, qz)
calcNetPressure = Cpe * qz
End Function

Function calcUDL(pn, s)
calcUDL = pn * s
End Function

Function calcMoment(w, L)
calcMoment = w * L ^ 2 / 8
End Function

Sub MainApplicationV10()
Dim Cpe
Dim qz
Dim s
Dim L

Dim pn
Dim w
Dim M

Dim key
Dim tmpStruct
Dim struct01

Set struct01 = CreateObject("Scripting.Dictionary")

'Input Parameters
Set Cpe = setStructuralCharacteristic("Cpe", "", 1)
Set qz = setStructuralCharacteristic("qz", "kPa", 2)
Set s = setStructuralCharacteristic("s", "m", 2)
Set L = setStructuralCharacteristic("L", "m", 2)

Cpe.Value = -0.7
qz.Value = 0.96
s.Value = 3
L.Value = 6

'Results
Set pn = setStructuralCharacteristic("pn", "kPa", 2)
Set w = setStructuralCharacteristic("w", "kN/m", 2)
Set M = setStructuralCharacteristic("M", "kNm", 2)

pn.Value = calcNetPressure(Cpe.Value, qz.Value)
w.Value = calcUDL(pn.Value, s.Value)
M.Value = calcMoment(w.Value, L.Value)

'Report
For Each key In struct01.Keys
Set tmpStruct = struct01.Item(key)
tmpStruct.cprint
Next

WScript.Echo "--------------"
struct01.Item("M").cprint
struct01("M").cprint
WScript.Echo struct01("Cpe").Value

End Sub

The functions now contain familiar looking formula, but the main program still obscures the simple calculation sequence. Still another way for the organising the code is as follows:

Option Explicit

Sub MainApplicationV11()
Dim Cpe
Dim qz
Dim s
Dim L

Dim pn
Dim w
Dim M

Dim key
Dim tmpStruct
Dim struct01

Set struct01 = CreateObject("Scripting.Dictionary")

'Input Parameters
Set tmpStruct = setStructuralCharacteristic("Cpe", "", 1)
Set tmpStruct = setStructuralCharacteristic("qz", "kPa", 2)
Set tmpStruct = setStructuralCharacteristic("s", "m", 2)
Set tmpStruct = setStructuralCharacteristic("L", "m", 2)

Cpe = -0.7
struct01("Cpe").Value = Cpe
qz = 0.96
struct01("qz").Value = qz
s = 3
struct01("s").Value = s
L = 6
struct01("L").Value = L

'Results
Set tmpStruct = setStructuralCharacteristic("pn", "kPa", 2)
Set tmpStruct = setStructuralCharacteristic("w", "kN/m", 2)
Set tmpStruct = setStructuralCharacteristic("M", "kNm", 2)

pn = Cpe * qz
struct01("pn").Value = pn
w = pn * s
struct01("w").Value = w
M = w * L ^ 2 / 8
struct01("M").Value = M

'Report
For Each key In struct01.Keys
Set tmpStruct = struct01.Item(key)
tmpStruct.cprint
Next

End Sub

In this approach each variable is added to the dictionary and simple variables are used for assignment of values and calculations, with values then assigned to the objects stored in the dictionary. In other words the dictionary is just an information store and the calculations can be done by other means. The only real value of the characteristic class is it contains extra information about each variable and assists with reporting: it otherwise creates clutter for the calculations. To reinforce that perspective here is a slightly modified version of the script.

Option Explicit

Sub MainApplicationV11B()
Dim Cpe
Dim qz
Dim s
Dim L

Dim pn
Dim w
Dim M

Dim key
Dim tmpStruct

Dim struct01

Set struct01 = CreateObject("Scripting.Dictionary")

'Set Up Data Store
'Input Parameters
Set tmpStruct = setStructuralCharacteristic("Cpe", "", 1)
Set tmpStruct = setStructuralCharacteristic("qz", "kPa", 2)
Set tmpStruct = setStructuralCharacteristic("s", "m", 2)
Set tmpStruct = setStructuralCharacteristic("L", "m", 2)
'Results
Set tmpStruct = setStructuralCharacteristic("pn", "kPa", 2)
Set tmpStruct = setStructuralCharacteristic("w", "kN/m", 2)
Set tmpStruct = setStructuralCharacteristic("M", "kNm", 2)

'Do Calculations
Cpe = -0.7
qz = 0.96
s = 3
L = 6
pn = Cpe * qz
w = pn * s
M = w * L ^ 2 / 8

'Store Data Values
struct01("Cpe").Value = Cpe
struct01("qz").Value = qz
struct01("s").Value = s
struct01("L").Value = L
struct01("pn").Value = pn
struct01("w").Value = w
struct01("M").Value = M

'Report
For Each key In struct01.Keys
Set tmpStruct = struct01.Item(key)
tmpStruct.cprint
Next
End Sub

This time round the calculations sequence is the same as the original simple script. The characteristic variables are created, added to the dictionary, then the values in the dictionary are updated to the new calculated and assigned values.

An alternative is just to create functions and routines for printing. The following is code from VBA

Option Explicit

Const MaxFieldLength As Integer = 40

Function sprintAttribute(Description As String, Name As String, Value As Variant) As String
Dim lengthDescr As Integer
Dim valueStr As String

'Wrap strings in brackets so can see when they are blank/empty
If VarType(Value) = vbString Then
valueStr = BracketStr1(CStr(Value))
Else
valueStr = CStr(Value)
End If

lengthDescr = Len(Description)
If lengthDescr <= MaxFieldLength Then
sprintAttribute = StrRPad(Description, MaxFieldLength) & ": " & Name & " = " & valueStr
Else 'trim description {Alternative is to mess up formatting and allow to overflow the column}
sprintAttribute = Mid(Description, 1, MaxFieldLength - 3) & "***" & ": " & Name & " = " & valueStr
End If

End Function

Sub cprintAttribute(Description As String, Name As String, Value As Variant)
Debug.Print sprintAttribute(Description, Name, Value)
End Sub

Function sprintCharacteristic(Description As String, Name As String, Value As Variant, Units As String, formatStr As String) As String
Dim lengthDescr As Integer

lengthDescr = Len(Description)
If lengthDescr <= MaxFieldLength Then
sprintCharacteristic = StrRPad(Description, MaxFieldLength) & ": " & Name & " = " & Format(Value, formatStr) & " " & Units
Else 'trim description
sprintCharacteristic = Mid(Description, 1, MaxFieldLength - 3) & "***" & ": " & Name & " = " & Format(Value, formatStr) & " " & Units
End If

End Function

Sub cprintCharacteristic(Description As String, Name As String, Value As Variant, Units As String, formatStr As String)
Debug.Print sprintCharacteristic(Description, Name, Value, Units, formatStr)
End Sub

Function sprintCharacteristicShort(Name As String, Value As Variant, Units As String, formatStr As String) As String
sprintCharacteristicShort = Name & " = " & Format(Value, formatStr) & " " & Units
End Function

Sub cprintCharacteristicShort(Name As String, Value As Variant, Units As String, formatStr As String)
Debug.Print sprintCharacteristicShort(Name, Value, Units, formatStr)
End Sub

With this approach can stay with simple variables, generate strings for print out and store the strings in an array, collection or dictionary. This is for use with the following primary class which I will be using for structural design: TBuildingStructure. Note that if used the characteristic class then then would only need to assign the variables and definitions to a dictionary once. The reporting to the immediate window (VBA), the command prompt (VBscript) , to a file, or to an Excel worksheet, would all be simple loops as used in the code above. It may therefore be beneficial to add the dictionary to track the variables for reporting purposes.

Option Explicit

Const pitchFormatRise As Integer = 0
Const pitchFormatDegrees As Integer = 1

Public numBays As Integer         '{Number of portal bays in length of building}
Public HeightEaves As Double      '{Eaves Height of Building}
Public width As Double            '{Over all Width of Building}

'Roof Elevation
Public PitchFmt As Double         '0 = Rise/run , 1 = degrees
Public Alpha As Double
Public SlopeRun As Double        '{Run of Roof Pitch}

Public BaySpace As Double     '{Centre to Centre Distance for Portal Frame columns}

Public ClearHghtEaves As Double
Public ClearHghtRidge As Double
Public ClearWidth As Double

Public CentreSpan As Double
Public CentreHeight As Double
Public CentreRidge As Double
Public CentreRise As Double
Public CentreRafter As Double

Public TotRoofRise As Double
Public RoofLength As Double

Public TribArea1 As Double
Public TribArea2 As Double
Public TribAreaColumn As Double

Public StructType As Integer
Public RoofType As Integer
Public StructClass As Integer
Public BCA_Class As Integer

Public PurlinRdge As Double      '=150 Purlin Spacing/distance from ridge
Public GirtFloor As Double       '= 200 Girt spacing/distance from floor
Public Ewall As Double           'Distance to end wall of building  from gridline

Dim memb(COLUMN To XBRACE2) As TStructMEMBER
Dim node(1 To MaxCoords) As TCoord

'{Over all Length of Building}
Property Get Length() As Double
Length = numBays * BaySpace
End Property

Dim SlopeRise As Double       '{=1; Rise of Roof Pitch}

Select Case PitchFmt
Case pitchFormatRise
SlopeRise = 1
Alpha = Atn(SlopeRise / SlopeRun)
Case pitchFormatDegrees
End Select

End Property

End Property

Property Get HeightRidge() As Double
Select Case RoofType
Case Skillion
HeightRidge = HeightEaves + width * Tan(AlphaRadians) 'Skillion

Case DoublyPitched
HeightRidge = HeightEaves + (width / 2) * Tan(AlphaRadians) 'Doubly Pitched

End Select
End Property

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

Property Get NumberBaysInEndWall() As Integer
NumberBaysInEndWall = Ceil(width / BaySpace)
End Property

Property Get EndWallBaySpacing()
EndWallBaySpacing = width / NumberBaysInEndWall
End Property

Sub initialise()
Dim i As Integer

numBays = 0
HeightEaves = 0
width = 0

BaySpace = 0

SlopeRun = 0

ClearHghtEaves = 0
ClearHghtRidge = 0
ClearWidth = 0
CentreSpan = 0
CentreHeight = 0
CentreRidge = 0
CentreRise = 0
CentreRafter = 0
TotRoofRise = 0
RoofLength = 0
TribArea1 = 0
TribArea2 = 0
TribAreaColumn = 0

PitchFmt = pitchFormatDegrees
StructType = 0
RoofType = DoublyPitched
StructClass = 0
BCA_Class = 0

For i = LBound(memb) To UBound(memb)
Set memb(i) = New TStructMEMBER
memb(i).initialise
Next i

For i = LBound(node) To UBound(node)
Set node(i) = New TCoord
Call node(i).SetCoord(0, 0, 0)
Next i

End Sub

'Set initial/Default dimensions defining building structure shape
Sub SetDefaultGableFrameGeometry()
width = 7.6 / milli
HeightEaves = 2.4 / milli
PitchFmt = pitchFormatRise
SlopeRun = 5 'Pitch 1:5

BaySpace = 3 / milli
numBays = 4
End Sub

'Framing Sizing: Smallest Expected to maximise calculated frame centres
'do not reference external database: hard code values
'The Kleinlogel Analysis only requires ratio between section properties
'For rafter and column the same section the ratio Ixx[Rafter]/Ixx[Column]=1
'The Kleinlogel analysis is passed hard coded values of 1, so don't need to set Ixx
Sub SetDefaultFrameSections()

'PORTAL FRAME SECTIONS: TRANSVERSE FRAMING
'Column
memb(COLUMN1).Name = "column"
memb(COLUMN1).Sect.Descr = "C100-10"
memb(COLUMN1).Sect.d = 100
memb(COLUMN1).Sect.swt = 1.77

'Bld.memb(COLUMN1).Sect.Ix = 1 'Only Ratio required by Kleinlogel Analysis so set to 1

'Rafter
memb(RAFTER).Name = "rafter"
memb(RAFTER).Sect.Descr = "C100-10"
memb(RAFTER).Sect.d = 100
memb(RAFTER).Sect.swt = 1.77

'Bld.memb(RAFTER).Sect.Ix = 1 'Only Ratio required by Kleinlogel Analysis so set to 1

'END WALL AND LONGITUDINAL FRAMING
'End Wall Mullion
memb(COLUMN2).Name = "mullion"
memb(COLUMN2).Sect.Descr = "C100-10"
memb(COLUMN2).Sect.d = 100
memb(COLUMN2).Sect.swt = 1.77

'Bld.memb(COLUMN2).Sect.Ix = 1 'Only Ratio required by Kleinlogel Analysis so set to 1

'Purlins
PurlinRdge = 150 / 1000
memb(PURLIN).Name = "purlin"
memb(PURLIN).Sect.Descr = "C75-10"
memb(PURLIN).Sect.d = 75
memb(PURLIN).Sect.swt = 1.248
memb(PURLIN).Spacing = 1200
PSpacing = memb(PURLIN).Spacing
memb(PURLIN).QtyR = 0
memb(PURLIN).Lap = 900

'Girts
GirtFloor = 200 / 1000
memb(GIRT).Name = "girt"
memb(GIRT).Sect.Descr = "C75-10"
memb(GIRT).Sect.d = 75
memb(GIRT).Sect.swt = 1.248
memb(GIRT).Spacing = 1200
memb(GIRT).QtyR = 0
memb(GIRT).Lap = 900
End Sub

'Set initial dimensions for building structure and components without reference to database
Sub SetDefaultBuildingAndFrameDimensions()
Debug.Print "SetDefaultBuildingAndFrameDimensions ..."
SetDefaultGableFrameGeometry

'NB: Frame Sections need to be initialised
'so that centre line dimension and geometry can be calculated
SetDefaultFrameSections

Debug.Print "... SetDefaultBuildingAndFrameDimensions"
End Sub

Sub cprint1()
Debug.Print "numBays= " & Format(numBays, "0")
Debug.Print "NumberBays In EndWall= " & Format(NumberBaysInEndWall, "0")

Debug.Print "HeightEaves= " & Format(HeightEaves, "0.0")
Debug.Print "width= " & Format(width, "0.0")
Debug.Print "length= " & Format(Length, "0.0")

Debug.Print "BaySpace= " & Format(BaySpace, "0.0")
Debug.Print "EndWallBaySpacing= " & Format(EndWallBaySpacing, "0.0")
Debug.Print "HeightRidge= " & Format(HeightRidge, "0.0")

Debug.Print "SlopeRun= " & Format(SlopeRun, "0.0")

Debug.Print "AverageHeight= " & Format(AverageHeight, "0.0")

Debug.Print "ClearHghtEaves= " & Format(ClearHghtEaves, "0.0")
Debug.Print "ClearHghtRidge= " & Format(ClearHghtRidge, "0.0")
Debug.Print "ClearWidth= " & Format(ClearWidth, "0.0")
Debug.Print "CentreSpan= " & Format(CentreSpan, "0.0")
Debug.Print "CentreHeight= " & Format(CentreHeight, "0.0")
Debug.Print "CentreRidge= " & Format(CentreRidge, "0.0")
Debug.Print "CentreRise= " & Format(CentreRise, "0.0")
Debug.Print "CentreRafter= " & Format(CentreRafter, "0.0")
Debug.Print "TotRoofRise= " & Format(TotRoofRise, "0.0")
Debug.Print "RoofLength= " & Format(RoofLength, "0.0")
Debug.Print "TribArea1= " & Format(TribArea1, "0.0")
Debug.Print "TribArea2= " & Format(TribArea2, "0.0")
Debug.Print "TribAreaColumn= " & Format(TribAreaColumn, "0.0")

Debug.Print "PitchFmt= " & Format(PitchFmt, "0")
Debug.Print "StructType= " & Format(StructType, "0")
Debug.Print "RoofType= " & Format(RoofType, "0")
Debug.Print "StructClass= " & Format(StructClass, "0")
Debug.Print "BCA_Class= " & Format(BCA_Class, "0")

End Sub

Sub cprint2()
Dim i As Integer

Call cprintCharacteristic("numBays", "numBays", numBays, "item", "0")
Call cprintCharacteristic("Number Bays In EndWall", "NumberBaysInEndWall", NumberBaysInEndWall, "item", "0")
Debug.Print

Call cprintCharacteristic("Height", "Height Eaves", HeightEaves, "mm", "0.0")
Call cprintCharacteristic("width", "width", width, "mm", "0.0")
Call cprintCharacteristic("length", "length", Length, "mm", "0.0")
Debug.Print

Call cprintAttribute("PitchFmt", "PitchFmt", PitchFmt)
Call cprintCharacteristic("SlopeRun", "SlopeRun", SlopeRun, "", "0.0")
Debug.Print

Call cprintCharacteristic("BaySpace", "BaySpace", BaySpace, "mm", "0.0")
Call cprintCharacteristic("EndWall BaySpacing", "EndWallBaySpacing", EndWallBaySpacing, "mm", "0.0")

Call cprintCharacteristic("Height Ridge", "HeightRidge", HeightRidge, "mm", "0.0")
Call cprintCharacteristic("Average Height", "AverageHeight", AverageHeight, "mm", "0.0")
Debug.Print

Call cprintCharacteristic("ClearHghtEaves", "ClearHghtEaves", ClearHghtEaves, "mm", "0.0")
Call cprintCharacteristic("ClearHghtRidge", "ClearHghtRidge", ClearHghtRidge, "mm", "0.0")
Call cprintCharacteristic("ClearWidth", "ClearWidth", ClearWidth, "mm", "0.0")
Debug.Print

Call cprintCharacteristic("CentreSpan", "CentreSpan", CentreSpan, "mm", "0.0")
Call cprintCharacteristic("CentreHeight", "CentreHeight", CentreHeight, "mm", "0.0")
Call cprintCharacteristic("CentreRidge", "CentreRidge", CentreRidge, "mm", "0.0")
Call cprintCharacteristic("CentreRise", "CentreRise", CentreRise, "mm", "0.0")
Call cprintCharacteristic("CentreRafter", "CentreRafter", CentreRafter, "mm", "0.0")
Debug.Print

Call cprintCharacteristic("TotRoofRise", "TotRoofRise", TotRoofRise, "mm", "0.0")
Call cprintCharacteristic("RoofLength", "RoofLength", RoofLength, "mm", "0.0")
Debug.Print

Call cprintCharacteristic("TribArea1", "TribArea1", TribArea1, "m^2", "0.0")
Call cprintCharacteristic("TribArea2", "TribArea2", TribArea2, "m^2", "0.0")
Call cprintCharacteristic("TribAreaColumn", "TribAreaColumn", TribAreaColumn, "m^2", "0.0")

Debug.Print
Call cprintAttribute("StructType", "StructType", StructType)
Call cprintAttribute("RoofType", "RoofType", RoofType)
Call cprintAttribute("StructClass", "StructClass", StructClass)
Call cprintAttribute("BCA_Class", "BCA_Class", BCA_Class)

'  Debug.Print
'  Debug.Print "Building Sections"
'  Debug.Print "-----------------"
'  For i = LBound(memb) To 5 'UBound(memb)
'    memb(i).cprint2
'  Next i
'
'  Debug.Print "Frame Nodes"
'  Debug.Print "-----------"
'  For i = LBound(node) To 5 'UBound(node)
'    Debug.Print i, node(i).sprint
'  Next i

End Sub

Sub CalculateGableFrameDimensions()
'Calculate Gable Frame Dimensions

'External Face
ClearHghtEaves = HeightEaves - _
((memb(PURLIN).Sect.d + memb(RAFTER).Sect.d) / 1000) / Cos(AlphaRadians) _
+ ((memb(GIRT).Sect.d + memb(COLUMN1).Sect.d) / 1000) * Tan(AlphaRadians)

ClearWidth = width - 2 * ((memb(GIRT).Sect.d + memb(COLUMN1).Sect.d) / 1000)

ClearHghtRidge = ClearHghtEaves + (ClearWidth / 2) * Tan(AlphaRadians)

CentreSpan = width - 2 * ((memb(GIRT).Sect.d + memb(COLUMN1).Sect.d / 2) / 1000)

CentreHeight = HeightEaves _
- (memb(RAFTER).Sect.d / 2 + memb(PURLIN).Sect.d) / 1000 / Cos(AlphaRadians) _
+ (memb(COLUMN1).Sect.d / 2 + memb(GIRT).Sect.d) / 1000 * Tan(AlphaRadians)

CentreRidge = CentreHeight + (CentreSpan / 2) * Tan(AlphaRadians)
CentreRise = CentreRidge - CentreHeight
CentreRafter = (CentreSpan / 2) / Cos(AlphaRadians)

RoofLength = (width / 2) / Cos(AlphaRadians)
TotRoofRise = (width / 2) * Tan(AlphaRadians)

memb(RAFTER).Le = ((width - 2 * (memb(GIRT).Sect.d / 1000)) / 2) / Cos(AlphaRadians)
End Sub

Sub CalculateBuildingLongitudinalDimensions()

Ewall = (memb(COLUMN2).Sect.d / 1000) / 2 + (memb(GIRT).Sect.d / 1000)

End Sub

Dim dx As Double, dy As Double
Dim np As Integer

'Calculate Remaining Dimensions

memb(PURLIN).Spacing = PSpacing
np = Ceil((memb(RAFTER).Le - 2 * PurlinRdge) / (memb(PURLIN).Spacing / 1000))
memb(PURLIN).Qty = np

memb(PURLIN).Spacing = ((memb(RAFTER).Le - 2 * PurlinRdge) / np) * 1000
End If

'Purlin Spacing for Calculating Coordinates

End Sub

'Convert all Dimensions to mm and radians}
Debug.Print "CalcDependentDimensions ..."
CalculateGableFrameDimensions
CalculateBuildingLongitudinalDimensions
Debug.Print "... CalcDependentDimensions"
End Sub

Sub getBuildingDimensions()

SetDefaultBuildingAndFrameDimensions
Call CalculateFrameDependentDimensions(False)

End Sub

'Calculate Remaining Dimensions
TribArea1 = (milli * width) / 2 * (milli * BaySpace)
TribArea2 = (milli * RoofLength) * (milli * BaySpace)
TribAreaColumn = (milli * BaySpace) * (milli * HeightEaves)

End Sub

The above code is from VBA and incomplete, it is mostly setup for gable frame (or doubly pitched roof shape), the intention is that it will allow other roof shapes, at the very minimum monoslope roofs, just as the equivalent worksheet does (or did at one time).

I didn’t write all the code out for the print statements, I used MS Excel worksheet cell formula to build up strings. Then copied the formula from the worksheet to the VBA editor. Which advises that may be doing something wrong, and that maybe the program should be reading data from a file or a worksheet. Other situations such as defining constants and enumerated values, largely stuck writing in the code. The current situation however the information could be read from a worksheet.

Not the least of which all the variables are typically stored in a worksheet, following the format of: description, variable name, value, units. Such worksheet can be imported to MS Access as a table. It is also suitable for export as an XML file. Plus it makes it easy to automatically assign the name the cell containing the value with the name in the cell to the left. An example of how the data originally appeared in Quattro Pro is shown below. Note that the data was also grouped into vertical blocks, and each block had a name at the top. These vertical blocks were used to store data collected from dialogue boxes.

With MS Excel it is much easier to set up data collection forms in the worksheet than it is to program dialogue boxes. However doing so can interfere with the sensible organisation of data for export and use by other applications. In the QPro example the horizontal heading bands and the vertical blocks hinder exporting the sheet to Dbase/Paradox.

The example worksheet from MS Excel, mixes up the parameter list and the data input form, making it difficult to extract the parameters to a data file for export and use elsewhere.

The need and desire to be able to export arose in the first instance because was carrying out calculations in Quattro Pro and developing parallel application in Delphi. The Delphi application however was primarily concerned with producing drawings in AutoCAD. Until I could get all structural calculations into Delphi I wanted the two programs to communicate at least via data files. So had parallel developments, which potentially wasted time.

When moved to MS Excel still largely continued the Delphi development, until needed to update wind loading calculations to AS1170.2:2002. Updating Excel/VBA had greater priority than updating Delphi and it then became more productive to convert all the remaining Delphi code to VBA and continue development in VBA and abandon Delphi.

However whilst using a worksheet, it is still preferable to split work into:

1. Data Input Form
2. Data Storage
3. Reporting

From my viewpoint the data input form shown above, created by my father, messed up my shed design workbook. The data input form has feedback information on it, which is good, but it has messed up the separation of parameters as raw data, it is cross-linked with my original tables, and there are now checks and tests all over the place.

I like order. I might start with chaos, but in the end I prefer order: that everything has a place and everything is in its place. To that end I have been writing VBA subroutines which can rip my shed design workbook apart and collect everything back into something I find more orderly.

Starting by be able to grab all the named ranges and export to a new workbook, with one worksheet for single cell variables, another for vertical lists, and another for tables. Only problem is some lists are horizontal and are part of the headers of tables. But that is a minor issue at the moment.

Additionally have two range names, a primary name and a secondary name derived from the primary by prefixing with “ed” or “ed_”. I generally don’t like underscores, but it is looking like using such is more preferable. This can be done automatically using cell formulae. The worksheet function indirect() can be used to get the value of the cell referenced by the derived name. This way the editor (ed) version of the variable can be anywhere, as best suits the creation of a data input form, whilst the real variable in in one place in the data table. The data table can also have two additional unnamed cells. One cell is for calculated value of a variable and the other is for an direct input override. For example in the sample calculation sequence I can calculate “w”, or I can just input its known value. Worksheet cell formula decide whether to assign the override value or the calculated value to the real variable.

Such additional cell for each variable is also useful if use dialogue boxes to collect data and want to cancel and retain the original values. From memory MS Excel dialogue boxes immediately change the value of cells, and the author of the dialogue needs to program the function of a cancel button: whilst with Quattro Pro if my memory is correct the dialogues already had “ok” and “cancel” buttons and the behaviour predetermined.

So basic approach therefore is that have one worksheet (table) for each VBA data record/class, with respect to using MS Excel as the data store. With respect to using MS Excel as input form and editor, then the input forms should be separate to the data store and use different range names (eg. prefix with “ed_”). Likewise the reporting should be separate to the editor and the data store.

Put another way, we create a simple calculator worksheet, and then connect that to an editor worksheet, and to a report worksheet. So each workbook requires a minimum of three worksheets.

Such approach is not required if simply calculating a simple formula. But not crunching numbers through a simple formula, rather designing an entire building structure: a shed, carport, house or similar. Furthermore not creating workbook for simple purpose of presenting calculations to show that we checked the rafter and column for combined bending and axial force. Want to do more than simply present calcs-to-council for approval. Rather want to model the building and see what happens when change a parameter.

For example if change the frame spacing then the span of the wall girts and roof purlins change. If increase the span then the depth of the girts and purlins increases. The increased depth of the girt may be considered as wasting floor space. The increased frame spacing reduces the number of frames installed on site, that is fewer footings to be drilled/bored: but they may be required to be larger diameter and deeper. But increased spans of girts and purlins may required reduced spacing as such, thus increasing number of girts and purlins to be installed. When the construction cost only considers the floor area and not the complexity of the assembly, then such parametric changes have limited impact on cost of construction. The result is focus tends to stay on cost of materials of main frame not the overall cost of materials. For example continuous span z-sections are seen as a waste of material and time, due to the lap and all the bolts it requires. No manufacturer however has ever shown me a detailed cost comparison, just expressed their opinion of too expensive.

So need to be better able to do costings based on structural assessment of alternative constructions. For certain ArchiCAD and Revit are the options for large buildings. But even when using such software still need the materials database, and still need to do some of the calculations outside such software. And still need some checks and balances, to test and interrogate the blackbox hidden behind such software.

So creating workbooks which work with vba, have the potential to export data and have that data imported into another workbook. That is the input parameters I used can be exported to a databook or xml file, then someone else can map my variable names to their variable names and import the data into their workbook and see if we then reach the same conclusions. Of course it would be better if we use the same variable names, but that is not entirely practical for a variety of reasons: not the least of which is I’m not changing them, as would take too long. Therefore need a mapping routine and map file, or extra field added to the data tables to allow for alternative variable name.

Things can be as complicated or simple as we choose or need to make them. The simple 7 line sequence of calculations, used as our reference, has been made increasingly complex by trying to read from files and write data to files, or otherwise get data from a user. Not even covered writing an editor to allow the user to edit data: our editor is taken to exist and be either a plain text editor or to be a spreadsheet application.

So next time will be a more comprehensive script carrying out more extensive sequence of calculations. It will use VBscript and the command line. If it needs input data it will be from XML files or MS Access: the first objective here is to move my calculations to a computer which doesn’t have MS Excel and noting that LibreOffice Basic is not fully compatible with VBA and therefore I cannot just port my technical library over to LibreOffice. (Thus far seems I need to modify the modules and also make sure subroutines are defined before they are called: and order seems to be dependent on order of the modules. So thus far library is being slowly converted and tested in FreeBasic which is even more restrictive, and the revised modules ported back to VBA.)

Revisions:

1. [21/12/2018] : Original (Only included V10 of code)
2. [22/12/2018] : Added more sample code (V4 to V11)