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 TypeThe 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 SubUsing 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 SubThe 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 SubNow 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 ClassThere 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 SubThe 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 SubIn 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 SubWith 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.
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:
- Data Input Form
- Data Storage
- 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:
- [21/12/2018] : Original (Only included V10 of code)
- [22/12/2018] : Added more sample code (V4 to V11)