Building a Structural (Engineering) Design eWorkbook – Part 7

So taking a look at the workbook primary data tables. The table comprises of fields: group, description, block Name, value, units, edit field, editValue, CalcValue, isParameter.

Block name, is carry over from Quattro Pro where group of cells referred to as a block rather than a range. In the main it is just the name assigned to a single cell, the cell to the right of the name. This allows the names to be automatically assigned to the cells by MS Excel.

The actual value of the variable is the editValue if it is not blank else it is the CalcValue. This is achieved with the formula: =IF(NOT(ISBLANK(editValue)),editValue,calcValue) . Where editValue and CalcValue are replaced by the appropriate cell reference.

The CalcValue cells use the formula INDIRECT(editField), where editfield is the name of the cell which is edited, located elsewhere in the workbook. The edit cell is typically named ed_VarName. If the variable isn’t an input parameter and needs to be calculated then CalcValue contains the calculated value. Cell formula refer to the variables not the edit fields, that is they use VarName not ed_VarName. The editValue cells allow the formula to be temporarily overridden.

The current simplified data table worksheet looks like the following:

It is similar to the earlier sheet but with some columns rearranged, and it lacks descriptions. To get data into the table for the parameters there is a dataform worksheet, which appears as follows:

The names of the edit fields don’t need to be in a column, however it assists in automatically naming and renaming the cells. The column can be hidden from view and row and column headers can also be removed:

Though such modifications tended to be better when MS Excel supported multi-document interface (MDI). Various other features of MS Excel can be switched off to modify the appearance and reduce to a simple input form, hiding all the calculation sheets.

Can also partly follow the approach taken in the VBA programming for generating a report, and use VLOOKUP to obtain value of variables and units. In the following image, the variable name is written in the left cell, and the contents on the right is retrieved from the data table (Variables) worksheet. It is only a simplistic report just demonstrating that the variables can be displayed in a multitude of different worksheet reports, from a centralised data store.

The calculations need section properties, using VBA these properties were obtained using DAO from MS Access. The alternative is to use another workbook or worksheet to collect the data. In this situation a worksheet is provided for member properties and section properties. Sticking to the matching requirements for export to MS Access these would be two separate tables, however it is cumbersome to link two worksheets together in MS Excel, so the two tables are placed on the one worksheet. {NB: I am not referring to the Table feature now built in to MS Excel. The data forms a table irrespective of any special properties MS Excel may apply to tables.}

Additional to the member property table is a material property table. The material table does not have strengths as these are typically obtained with the section properties.

Additional to these worksheets, is a worksheet for graphic display or parametric sketching of the data. Use of XY charts helps to check that dimensions produce the expected results.

The data input form allows the roof type to be changed and when it is the calculations reflect the geometry of the new roof shape. Besides displaying the shape on an XY-chart, to the right of the coordinate calculations is script for use with Acad LT 2000 or ProgeCAD 2016 or similar. This script can simply be copy and pasted to the command line of compatible CAD packages and it will draw the frame outline: the underside of the cladding, the outer face of the frame, the inner face of the frame, and the centre line of the frame.

The worksheet calculations check the VBA calculations, and the XY plots and the CAD drawings provide further checks on dimensional calculations.

Whilst worksheets and XY-charts provide a quick way to work through generating parametric sketches, XY-charts are limited in what they can display. For example could modify the charts to show double span building, and make use of the NA() function to make one span appear or disappear. It is however a lot of extra work to add an extra span, where as it is relatively easy to add a loop in VBA and generate an elevation for any number of spans. Similarly can draw a framing plan using an XY chart, but it is cumbersome to add extra bays.

Therefore if need greater flexibility, more design options then it is preferable to do the calculations in VBA. Alternatively the calculations could be done in a worksheet, has they have been, and then write VBA to read the results and generate a script for AcadLT/ProgeCAD. However the formula are already getting over long and cumbersome with two options (gable, skillion), to add more options would make them even more cumbersome.

So just because can do something in a worksheet using cell formula doesn’t mean we should. Similarly just because we can do it using VBA or other programming language doesn’t mean we should.

Evolution of a Spreadsheet Application

Things tend to start with worksheets because it comes close to matching our use of pencil, paper and a pocket calculator: more so than the likes of MathCAD. We pick up a traditional pocket calculator and punch numbers through it, we don’t write anything down anywhere and we don’t type anything into the calculator other than the numbers and operators. MathCAD typically requires dreaming up variable names, if there is to be any referencing, a spreadsheet doesn’t require such hindrance, just punch numbers into cells and point to them. The cells already have variable names in the form of their default address.

Irrespective of whether started the calculations with MathCAD or a spreadsheet, the initial objective was most likely to document calculations, largely ignoring that calculations are a means to an end. That the results of the calculations are required to make decisions and take action. Printing pretty calculations on a piece of paper contributes little to automating decisions and actions. However when automating such things also need to allow for human interaction and override: possibly implementing an intentional delay period. For example do not want automated rollforms fabricating the steel for a shed, when didn’t modify all the input parameters and made erroneous assumptions about the default values, and didn’t adequately check and confirm requirements. Even with such delay the automated approach is likely to be faster than the non-automated approach.

So can dive straight into doing worksheet calculations, but can we extract the data for use elsewhere? How easy is it to find and automatically extract the data? Can I avoid repetition in the coding? Is the repetition an essential part of checks and balances?

Back to VBA

So back to the VBA. I modified the TBuildingStructure class from the previous post.

Option Explicit

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

Public dx As Double, dy As Double

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

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

Dim varDict As Scripting.Dictionary


Sub initialise()
  Dim i As Integer
  Dim tmpCharacteristic As PhysicalCharacteristic
  
  On Error GoTo Err_initialise

  numBays = 0
  HeightEaves = 0
  width = 0
  
  BaySpace = 0

  SlopeRun = 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
  
  memb(PURLIN).Spacing = 1200
  memb(GIRT).Spacing = 1200
  
  For i = LBound(node) To UBound(node)
    Set node(i) = New TCoord
    Call node(i).SetCoord(0, 0, 0)
  Next i
  
  Set varDict = New Scripting.Dictionary
  
  Call varDict.Add("PitchFmt", setgetCharacteristic("PitchFmt", "", ""))
  Call varDict.Add("StructType", setgetCharacteristic("StructType", "", ""))
  Call varDict.Add("RoofType", setgetCharacteristic("RoofType", "", ""))
  Call varDict.Add("StructClass", setgetCharacteristic("StructClass", "", ""))
  Call varDict.Add("BCA_Class", setgetCharacteristic("BCA_Class", "", ""))
  
  
  Call varDict.Add("width", setgetCharacteristic("width", "m", "0.000"))
  
  Call varDict.Add("HeightEaves", setgetCharacteristic("HeightEaves", "m", "0.000"))
  Call varDict.Add("HeightRidge", setgetCharacteristic("HeightRidge", "m", "0.000"))
  Call varDict.Add("AverageHeight", setgetCharacteristic("AverageHeight", "m", "0.000"))
  Call varDict.Add("TotRoofRise", setgetCharacteristic("TotRoofRise", "m", "0.000"))
  Call varDict.Add("RoofLength", setgetCharacteristic("RoofLength", "m", "0.000"))
  
  Call varDict.Add("SlopeRun", setgetCharacteristic("SlopeRun", "m", "0.000"))
  Call varDict.Add("AlphaDegrees", setgetCharacteristic("AlphaDegrees", "degrees", "0.0"))
  Call varDict.Add("AlphaRadians", setgetCharacteristic("AlphaRadians", "radians", "0.0000"))
  
  Call varDict.Add("numBays", setgetCharacteristic("numBays", "", ""))
  Call varDict.Add("BaySpace", setgetCharacteristic("BaySpace", "m", "0.000"))
  Call varDict.Add("NumberBaysInEndWall", setgetCharacteristic("NumberBaysInEndWall", "", "0.000"))
  Call varDict.Add("EndWallBaySpacing", setgetCharacteristic("EndWallBaySpacing", "m", "0.000"))
  Call varDict.Add("length", setgetCharacteristic("length", "m", "0.000"))
  
 
  Call varDict.Add("ClearHghtEaves", setgetCharacteristic("ClearHghtEaves", "m", "0.000"))
  Call varDict.Add("ClearWidth", setgetCharacteristic("ClearWidth", "m", "0.000"))
  Call varDict.Add("ClearHghtRidge", setgetCharacteristic("ClearHghtRidge", "m", "0.000"))

  Call varDict.Add("CentreSpan", setgetCharacteristic("CentreSpan", "m", "0.000"))
  Call varDict.Add("CentreHeight", setgetCharacteristic("CentreHeight", "m", "0.000"))
  Call varDict.Add("CentreRidge", setgetCharacteristic("CentreRidge", "m", "0.000"))
  Call varDict.Add("CentreRise", setgetCharacteristic("CentreRise", "m", "0.000"))
  Call varDict.Add("CentreRafter", setgetCharacteristic("CentreRafter", "m", "0.000"))
  
  Call varDict.Add("outerWidth", setgetCharacteristic("outerWidth", "m", "0.000"))
  Call varDict.Add("outerRidgeHeight", setgetCharacteristic("outerRidgeHeight", "m", "0.000"))
  Call varDict.Add("outerKneeHeight", setgetCharacteristic("outerKneeHeight", "m", "0.000"))
  

  Call varDict.Add("TribArea1", setgetCharacteristic("TribArea1", "m^2", "0.00"))
  Call varDict.Add("TribArea2", setgetCharacteristic("TribArea2", "m^2", "0.00"))
  Call varDict.Add("TribAreaColumn", setgetCharacteristic("TribAreaColumn", "m^2", "0.00"))
  
  Call varDict.Add("Ewall", setgetCharacteristic("Ewall", "mm", "0"))
  
  
Exit_initialise:
  Exit Sub

Err_initialise:
  Close
  Call ErrorMessages("initialise")
  Stop
  
End Sub

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

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

    BaySpace = 3 'm
    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 = 102
    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 = 102
    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 = 102
    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 'm
    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 'mm
    PSpacing = memb(PURLIN).Spacing
    memb(PURLIN).QtyR = 0
    memb(PURLIN).Lap = 900 'mm

    'Girts
    GirtFloor = 200 / 1000 'm
    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 'mm
    memb(GIRT).QtyR = 0
    memb(GIRT).Lap = 900 'mm
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

Property Get ClearHghtEaves() As Double
      ClearHghtEaves = HeightEaves - _
          (milli * (memb(PURLIN).Sect.d + memb(RAFTER).Sect.d)) / Cos(AlphaRadians) _
          + (milli * (memb(GIRT).Sect.d + memb(COLUMN1).Sect.d)) * Tan(AlphaRadians)
End Property

Property Get ClearWidth() As Double
      ClearWidth = width - 2 * (milli * (memb(GIRT).Sect.d + memb(COLUMN1).Sect.d))
End Property


Property Get ClearHghtRidge() As Double
  Select Case RoofType
    Case DoublyPitched
      ClearHghtRidge = ClearHghtEaves + (ClearWidth / 2) * Tan(AlphaRadians)
    Case Skillion
      ClearHghtRidge = ClearHghtEaves + ClearWidth * Tan(AlphaRadians)
  End Select
End Property

Property Get CentreSpan() As Double
  CentreSpan = width - 2 * (milli * (memb(GIRT).Sect.d + memb(COLUMN1).Sect.d / 2))
End Property

Property Get CentreHeight() As Double
  CentreHeight = HeightEaves _
   - milli * (memb(RAFTER).Sect.d / 2 + memb(PURLIN).Sect.d) / Cos(AlphaRadians) _
   + milli * (memb(COLUMN1).Sect.d / 2 + memb(GIRT).Sect.d) * Tan(AlphaRadians)
End Property

Property Get CentreRidge() As Double
  Select Case RoofType
    Case DoublyPitched
      CentreRidge = CentreHeight + (CentreSpan / 2) * Tan(AlphaRadians)
    Case Skillion
      CentreRidge = CentreHeight + CentreSpan * Tan(AlphaRadians)
  End Select
End Property

Property Get CentreRise() As Double
  CentreRise = CentreRidge - CentreHeight
End Property


Property Get CentreRafter() As Double
  Select Case RoofType
    Case DoublyPitched
      CentreRafter = (CentreSpan / 2) / Cos(AlphaRadians)
    Case Skillion
      CentreRafter = CentreSpan / Cos(AlphaRadians)
  End Select
End Property

Property Get RoofLength()
  Select Case RoofType
    Case DoublyPitched
      RoofLength = (width / 2) / Cos(AlphaRadians)
    Case Skillion
      RoofLength = width / Cos(AlphaRadians)
  End Select
End Property

Property Get TotRoofRise() As Double
  Select Case RoofType
    Case DoublyPitched
      TotRoofRise = (width / 2) * Tan(AlphaRadians)
    Case Skillion
      TotRoofRise = width * Tan(AlphaRadians)
  End Select
End Property

'Approximate: assuming it fits between the columns
Property Get RafterLength() As Double
  Select Case RoofType
    Case DoublyPitched
      memb(RAFTER).Le = ((width - 2 * (milli * memb(GIRT).Sect.d)) / 2) / Cos(AlphaRadians)
      RafterLength = memb(RAFTER).Le
    Case Skillion
      memb(RAFTER).Le = (width - 2 * (milli * memb(GIRT).Sect.d)) / Cos(AlphaRadians)
      RafterLength = memb(RAFTER).Le
  End Select
End Property

'Distance to end wall of building  from gridline
Property Get Ewall() As Double
      Ewall = (memb(COLUMN2).Sect.d / 2) + memb(GIRT).Sect.d
End Property

Property Get TribArea1() As Double
  TribArea1 = (width / 2) * BaySpace
End Property

Property Get TribArea2() As Double
  TribArea2 = RoofLength * BaySpace
End Property

Property Get TribAreaColumn() As Double
  TribAreaColumn = BaySpace * HeightEaves
End Property

Property Get outerWidth() As Double
  outerWidth = width - 2 * milli * memb(GIRT).Sect.d
End Property

Property Get outerRidgeHeight() As Double
  Select Case RoofType
    Case DoublyPitched
      outerRidgeHeight = HeightRidge - milli * memb(PURLIN).Sect.d / Cos(AlphaRadians)
    Case Skillion
      outerRidgeHeight = HeightRidge - milli * memb(PURLIN).Sect.d / Cos(AlphaRadians) - milli * memb(GIRT).Sect.d * Tan(AlphaRadians)
  End Select
End Property

Property Get outerKneeHeight() As Double
  Select Case RoofType
    Case DoublyPitched
      outerKneeHeight = outerRidgeHeight - (outerWidth / 2) * Tan(AlphaRadians)
    Case Skillion
      outerKneeHeight = outerRidgeHeight - outerWidth * Tan(AlphaRadians)
  End Select
End Property

 
Sub CalculateFrameDimensions()
  'UpDate Variable Dictionary
  'NB:Preferable updated as individual values are updated
  varDict("PitchFmt").Value = PitchFmt
  varDict("StructType").Value = StructType
  varDict("RoofType").Value = RoofType
  varDict("StructClass").Value = StructClass
  varDict("BCA_Class").Value = BCA_Class
  
  varDict("numBays").Value = numBays
  varDict("NumberBaysInEndWall").Value = NumberBaysInEndWall
  varDict("HeightEaves").Value = HeightEaves
  varDict("HeightRidge").Value = HeightRidge
  varDict("AverageHeight").Value = AverageHeight
  
  varDict("width").Value = width
  varDict("length").Value = Length
  
  varDict("SlopeRun").Value = SlopeRun
  varDict("AlphaDegrees").Value = AlphaDegrees
  varDict("AlphaRadians").Value = AlphaRadians
  varDict("BaySpace").Value = BaySpace
  varDict("EndWallBaySpacing").Value = EndWallBaySpacing
  
  
  
  varDict("ClearHghtEaves").Value = ClearHghtEaves
  varDict("ClearWidth").Value = ClearWidth
  varDict("ClearHghtRidge").Value = ClearHghtRidge
  varDict("CentreSpan").Value = CentreSpan
  varDict("CentreHeight").Value = CentreHeight
  varDict("CentreRidge").Value = CentreRidge
  varDict("CentreRise").Value = CentreRise
  varDict("CentreRafter").Value = CentreRafter
  varDict("TotRoofRise").Value = TotRoofRise
  varDict("RoofLength").Value = RoofLength
  
  varDict("outerWidth").Value = outerWidth
  varDict("outerRidgeHeight").Value = outerRidgeHeight
  varDict("outerKneeHeight").Value = outerKneeHeight
  
  varDict("TribArea1").Value = TribArea1
  varDict("TribArea2").Value = TribArea2
  varDict("TribAreaColumn").Value = TribAreaColumn
 
  varDict("Ewall").Value = Ewall
End Sub

'Purlin Spacing Assumed Set
Property Get NumberOfPurlins() As Integer
  Dim np As Integer
  
  np = Ceil((memb(RAFTER).Le - 2 * PurlinRdge) / (milli * memb(PURLIN).Spacing))
  memb(PURLIN).Qty = np
  NumberOfPurlins = memb(PURLIN).Qty
  
End Property

Property Get PurlinSpacing(ByVal isSpacingAdjusted As Boolean) As Double
  If isSpacingAdjusted Then
    memb(PURLIN).Spacing = ((memb(RAFTER).Le - 2 * PurlinRdge) / NumberOfPurlins) * 1000
  Else
    PurlinSpacing = memb(PURLIN).Spacing
  End If
End Property


'Calculate Remaining Dimensions
Sub CalculateBuildingCladdingDimensions(ByVal isAdjustSpacing As Boolean)

  'Purlin Spacing for Calculating Coordinates
  dx = PurlinSpacing(isAdjustSpacing) * Cos(AlphaRadians)
  dy = PurlinSpacing(isAdjustSpacing) * Sin(AlphaRadians)

End Sub


'Convert all Dimensions to mm and radians}
Sub CalculateFrameDependentDimensions(ByVal isAdjustSpacing As Boolean)
    Debug.Print "CalcDependentDimensions ..."
    CalculateFrameDimensions
    Call CalculateBuildingCladdingDimensions(isAdjustSpacing)
    Debug.Print "... CalcDependentDimensions"
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
  Dim key As Variant
  Dim tmpCharacteristic As PhysicalCharacteristic
  
  'Report
  For Each key In varDict.Keys
    Set tmpCharacteristic = varDict.Item(key)
    tmpCharacteristic.cprint
  Next key
  
'  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

The main program routine using this class is MainTask for want of a better name at this point. Preceding this subroutine is MainApplication and cMain. These are not necessary they are just messing around attempting to use the VBA editor immediate window as the windows command prompt and reflect the approach taken when using VBscript. The subroutine cMain could be called from the immediate window as follows: cmain “sample.dat”. Though from the windows command generally don’t need the quotes. To save typing however this is written in MainApplication, and the macro can be selected from tools/macros, alternatively I could put a button on the worksheet. Still further I can add the facility for selecting a file with a dialogue box. So from cMain down will eventually convert to VBscript, whilst MainApplication doesn’t need converting: more over it could run the VBscript version of cMain.

Sub MainTask(ByVal ifullName As String, ByVal ofullName As String, ByVal TraceFName As String)
  On Error GoTo Err_MainTask
  Debug.Print "MainTask ..."
  
  Debug.Print "... Do Something ..."

'  Set bldMem = New TStructMEMBER
'  With bldMem
'    .initialise
'    Debug.Print .Sect.MtrlDB
'
'    .Le = 12 / milli
'
'    If .Sect.getSectProp("C200-15") Then
'      .cprint2
'    End If
'  End With
  
  Debug.Print
  Debug.Print "BUILDING"
  Debug.Print "--------"
  Set Bld = New TBuildingStructure
  With Bld
    .initialise
    .SetDefaultBuildingAndFrameDimensions
    .RoofType = Skillion
    Call .CalculateFrameDependentDimensions(False)
    
    .cprint2
  End With
  
  Debug.Print "...MainTask"
  
Exit_MainTask:
  Exit Sub

Err_MainTask:
  Close
  Call ErrorMessages("MainTask")
  Stop
  
End Sub

So at the moment there is no data input, the test is just using default data set through the class. So the worksheet can easily vary the data, whilst noting the VBA code has no means of getting new data other than editing the code. the default data assumes a gable roof, so the setting of .rooftype=DoublyPitched is not necessary, just comment out the statement which sets to skillion.

When started I was going to convert to use of millimetres, but decided to stay with metres. This messed up the print routine. If have more than one routine, such as one to print to screen and one to print to file,then that is a lot of changes to make. I therefore added a dictionary object to assist with printing, all variables and their units are stored in the dictionary, the current calculated values need updating in the dictionary. This is somewhat messy as the stored values in the dictionary do not necessarily match the current value: the dictionary needs to be kept up to date. An alternative is that I make all the variables of the characteristic class, as mentioned in the earlier posts: but this creates other hindrances.

Also to be noted is that as part of a class, none of the functions are available for use in the worksheet. So the contents of the class are hidden from the worksheet, this may be good if want to hide from end-users. It is a problem however if objective is to provide functions which extend worksheet calculations and allow free formatting of custom reports. Here the objective isn’t to produce reports but to drive other software.

Relationship to Existing Workbooks

If take a look at the spreadsheet bundle then the following are of interest:

1. drawshed.zip: this extends on the above dimensional calculations and draws 2D framing plans for a gable roof shed. It uses AutoCAD scripts.
2. drawShedDC1.zip: this extends the dimensional calculations, it draws a 3D stick diagram of an American Barn Style shed.
3. ExcelShapes.zip: this extends the dimensional calculations and attempts to draw 2D framing plans using the MS Excel shapes layer. {It is not complete}
4. schBuildingDimensions.xls : this extends the dimensional calculations to determine the lengths of the structural members in a cold-formed steel shed. The actual members are not mitred they are straight cut.
5. schShedDesignerR01.xls : This uses the centre line geometry for input to Kleinlogel formula for a gable frame shed.
6. schDesignEngineR01.zip : This uses the centre line geometry, and kleinlogel formulae to determine height span limits of various structural sections.
7. There is also the plane frame analysis application, in MS Excel and various high level programming and scripting languages.

Each of the above workbook applications is disconnected. That is the frame design doesn’t produce the drawings, and the drawings aren’t dependent on the frame design. The workbook for detailed dimensional check is also not connected to frame analysis or drawing applications. Each is independent, likely replicates calculations, and requires data to be manually re-entered. In short the workbooks are building blocks available to make something more complete.

On Adoption of Calculation Tools in Education

By now I expect that school pupils are using the likes of LibreOffice Calc, SMath, Freemat, scilab or python in their work. Sure pencil and paper is easier to use if have a mathematical problem to set out and solve, but why use a pocket calculator for the number crunching? As for cheating? I forget why do we invent technology, what is its purpose?

Mathematics, such as algebra can be introduced earlier, useful, practical, technical mathematics. By the time they leave school they should have a box of useful calculation tools they can put to use. As they go into higher education that toolbox should grow. The tool box however should be growing and developing relative to their interests: it should be far more than a simple collection of worksheets with formulae. It should be unique to their specific needs.

More calculations can be introduced into various subjects, because the calculations are easier to do and faster to do. For example calorie counting in meals and costing such meals for home economics. Calorie use in physical education. Balancing the two in health studies, as well as checking nutritional content.

Integrating subjects through the use of mathematics and science. How far can you walk, how far can you cycle, how much water do you need? All their studies have the potential to be more permanent than those documented with pencil and paper, and consequently they can be built upon. Where do the food calories come from, what resources are required to produce them?

The youngest pupil can read a thermometer and other instruments and record the readings on a computer. Such collected data can be used at some future date. The data stored on a central school server, or out in the cloud, mixed with data from other schools. Does everybody doing experiments this year get the same results? Did students last year get the same results? Processing so called big data becomes a matter of routine.

The important issue however is that they build the tools they need using more general purpose tools. For the most part we don’t have problems to solve, we have solutions which need adapting and implementing.

---

Revisions:

1. [24/12/2018] : Original
2. [27/12/2018] : Some minor revisions