Units for Excel 4: Scripting dictionaries

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

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

This entry was posted in Excel, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

6 Responses to Units for Excel 4: Scripting dictionaries

  1. Brandon Schwabauer says:


    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!


    • dougaj4 says:

      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.


      • Brandon Schwabauer says:

        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!


  2. Pingback: Units for Excel 5: | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Daily Download 24: Units for Excel | Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: Dictionary link | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.