As mentioned in the previous post I have now updated the Units4Excel spreadsheet with the use of scripting dictionaries to speed up the process of finding units in the lists, and also to simplify the process of adding or deleting units. The revised spreadsheet may be downloaded from Units4Excel including full open source code.
The use of scripting dictionaries has been described previously at Working with FEM data and using the dictionary object:
- Each entry in the dictionary has a string “key” and an associated “item” which may be any data type. In this application the item is used to store the index number of each unit in the unit lists.
- The “Exists” method is used to determine if a unit is included in the dictionary object, and if so its position in the table.
Generation of the dictionary for the Non-SI units list is shown below:
... NumNonSI = UBound(NonSIUnits) Set NonSIDict = New Scripting.Dictionary For i = 1 To NumNonSI NonSIAbbr = NonSIUnits(i, 2) If NonSIDict.Exists(Key:=NonSIAbbr) = False Then NonSIDict.Add NonSIAbbr, i Next i ...
And the dictionary is used to return the index number for the specified unit, then read the required details from the NonSIUnits array, as shown below:
...
' If the NonSIUnits array is empty, run the ReadUnits function
On Error GoTo UnitErr
NumRows = UBound(NonSIUnits)
If NumRows = 0 Then GoTo UnitErr
On Error GoTo RtnError
If NonSIDict.Exists(UnitName) Then
i = NonSIDict.Item(UnitName)
For j = 1 To 5
UnitA(1, j) = NonSIUnits(i, j)
Next j
Else
UnitA(1, 1) = "Unit Not listed"
End If
NonSI = UnitA
Having implemented the dictionaries, the row numbers previously generated on the spreadsheet to locate each unit category in the Non-SI Units list have become redundant and have been deleted, removing a possible source of error.
In the next post I will describe how the units UDFs can be incorporated in other functions to give them the ability to work with any of the units listed; using the concrete beam design spreadsheet as an example (see Reinforced Concrete Section Analysis – 6; Ultimate Limit State).
Newton Excel Bach, not (just) an Excel Blog 