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 
Doug,
Thanks for the wonderful blog! I have learned much from your articles. I find this units spreadsheet concept great! However, I had some issues getting it running correctly. When I first opened the spreadsheet and edited any of the values under the EvalU sheet, any formula with EvalU() in it gave a #Value error. Eval() functions functioned correctly. So, I put a Msgbox after this section to make sure it was firing:
GetArray ParamA
Eform = Func
Units = Func
NumParam = UBound(ParamA)
ReDim UnitList(1 To NumParam, 1 To 3)
NumBaseSI = UBound(SIDims, 2) – 1
The message box never popped up after I updated values on the spreadsheet. So I moved the Msgbox to before that section, and updated values and low and behold it worked. I took out the message box and sure enough it still worked. Then I moved the message box to after that section again, and it still worked. It works until I close the file. On opening, the error persists. Do you have any insight into this issue?
Also, is there a way to default it to returning the appropriate Non-SI Units. So instead of defaulting to m2 it would default to in2 or ft2?
Thanks again!
LikeLike
Hi Brandon, thanks for the comments and for the feedback on the spreadsheet.
The problem was that there was supposed to be a routine fired by the Workbook_open event to initialise the units dictionaries, but this seems to have got lost at some stage. I didn’t notice because the other functions (other than Evalu) have routines to run the initialise dictionaries routines if they are empty or don’t exist. I had been using these other functions first, so when I came to use Evalu the dictionaries were available and the function worked without a problem.
I have now added the required routine to the Workbook_open event in the ThisWorkbook module, and it seems to be working OK. I have also added the error handling to EvalU. You can download the new file from the link at the top of the post.
Please let me know if you still have any problems on your machine.
As for changing the default output units, at the moment you have to specify the units for anything other than SI, but for the next version I will look at putting a default units table on the spreadsheet, so you can use anything you want.
LikeLike
Thanks for the response! The new spreadsheet works great so far! I will continue to work with it and let you know if I find anything else. Thanks again for your excellent blog!
LikeLike
Pingback: Units for Excel 5: | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 24: Units for Excel | Newton Excel Bach, not (just) an Excel Blog
Pingback: Dictionary link | Newton Excel Bach, not (just) an Excel Blog