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:
- drawshed.zip: this extends on the above dimensional calculations and draws 2D framing plans for a gable roof shed. It uses AutoCAD scripts.
- drawShedDC1.zip: this extends the dimensional calculations, it draws a 3D stick diagram of an American Barn Style shed.
- ExcelShapes.zip: this extends the dimensional calculations and attempts to draw 2D framing plans using the MS Excel shapes layer. {It is not complete}
- 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.
- schShedDesignerR01.xls : This uses the centre line geometry for input to Kleinlogel formula for a gable frame shed.
- schDesignEngineR01.zip : This uses the centre line geometry, and kleinlogel formulae to determine height span limits of various structural sections.
- 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:
- [24/12/2018] : Original
- [27/12/2018] : Some minor revisions