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)
  
  Call struct01.Add(Cpe.Name, Cpe)
  Call struct01.Add(qz.Name, qz)
  Call struct01.Add(s.Name, s)
  Call struct01.Add(L.Name, L)
  
  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)
  
  Call struct01.Add(pn.Name, pn)
  Call struct01.Add(w.Name, w)
  Call struct01.Add(M.Name, M)
  
  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)
  
  Call struct01.Add(Cpe.Name, Cpe)
  Call struct01.Add(qz.Name, qz)
  Call struct01.Add(s.Name, s)
  Call struct01.Add(L.Name, L)
  
  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)
  
  Call struct01.Add(pn.Name, pn)
  Call struct01.Add(w.Name, w)
  Call struct01.Add(M.Name, M)
  
  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)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("qz", "kPa", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("s", "m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("L", "m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
      
  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)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("w", "kN/m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("M", "kNm", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  
  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)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("qz", "kPa", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("s", "m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("L", "m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  'Results
  Set tmpStruct = setStructuralCharacteristic("pn", "kPa", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("w", "kN/m", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  Set tmpStruct = setStructuralCharacteristic("M", "kNm", 2)
  Call struct01.Add(tmpStruct.Name, tmpStruct)
  
  '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

Property Get AlphaDegrees() As Double
  Dim SlopeRise As Double       '{=1; Rise of Roof Pitch}
  
  Select Case PitchFmt
    Case pitchFormatRise
      SlopeRise = 1
      Alpha = Atn(SlopeRise / SlopeRun)
      AlphaDegrees = ToDegrees(Alpha)
    Case pitchFormatDegrees
      AlphaDegrees = Alpha
  End Select
  
End Property

Property Get AlphaRadians() As Double
  AlphaRadians = ToRadians(AlphaDegrees)
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

    'ROOF and WALL CLADDING RAILS
    '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 "AlphaDegrees= " & Format(AlphaDegrees, "0.0")
  Debug.Print "AlphaRadians= " & Format(AlphaRadians, "0.0000")
  
  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")
  Call cprintCharacteristic("AlphaDegrees", "AlphaDegrees", AlphaDegrees, "degrees", "0.0")
  Call cprintCharacteristic("AlphaRadians", "AlphaRadians", AlphaRadians, "radians", "0.0000")
  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

Sub CalculateBuildingCladdingDimensions(ByVal isAdjustSpacing As Boolean)
  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
  
  If isAdjustSpacing Then
      memb(PURLIN).Spacing = ((memb(RAFTER).Le - 2 * PurlinRdge) / np) * 1000
  End If
  
  'Purlin Spacing for Calculating Coordinates
  dx = memb(PURLIN).Spacing * Cos(AlphaRadians)
  dy = memb(PURLIN).Spacing * Sin(AlphaRadians)

End Sub


'Convert all Dimensions to mm and radians}
Sub CalculateFrameDependentDimensions(ByVal isAdjustSpacing As Boolean)
    Debug.Print "CalcDependentDimensions ..."
    CalculateGableFrameDimensions
    CalculateBuildingLongitudinalDimensions
    Call CalculateBuildingCladdingDimensions(isAdjustSpacing)
    Debug.Print "... CalcDependentDimensions"
End Sub


Sub getBuildingDimensions()

    SetDefaultBuildingAndFrameDimensions
    Call CalculateFrameDependentDimensions(False)

End Sub

Sub CalculateLoadingDimensions()

  '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.

Using MS Excel as an Aid to Writing Programming/Scripting Source Code

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.

Early Example of Data In Quattro Pro version 1 for Windows

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.

Example Data Input Form MS Excel

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)