Units for Excel 3 – Editing the units table

I have made several changes to the Units4Excel spreadsheet:

  • Litres added as the base SI unit of fluid volume.
  • Units added for specific heat
  • Units added for fuel consumption in both km/litre and litres/100 km form

As before, the revisions are currently only in the standalone version; the add-in version will be updated at a later date. The revised spreadsheet may be downloaded from: Units4Excel

Since there are a huge number of other units in use in various contexts it is difficult, if not impossible, to cater for all eventualities, so I will describe how the unit tables may be edited to change unit names or abbreviations and to introduce new units.

The editable tables are all on the “Ext Unit List” and “SIDims” sheets, the tables on the “XLUnit_List” sheet are the units used by the built-in Convert function, and are for reference only.  Editing the Non-SI units table for existing units or unit types is straightforward; for instance:

To change default gallon units from Imperial to US fluid units change the table as shown below:

Existing Gallon units

Gallon Units modified for US fluid gallon default

     To insert a new unit, with an existing unit type, insert a blank row in the appropriate range, and enter details following the pattern of the other entries.
    Entering a new unit type is a little more complicated.  The screen shots below show the addition of the new “fuel consumption” and “inverse fuel consumption” units:

Insert the new unit details in the Non-SI Units Table.

Insert rows for the new unit types in the SI Units table

Add unit details. Note that the row numbers in Col F and G are generated by functions copied from the row below

Add new rows in the “Dimensions of SI Units” Table

In order  to recognise the new units the spreadsheet must be saved and re-opened.

In future versions this process will be simplified, using the VBA “scripting dictionary” object, which will also improve performance, but the current system allows the addition of any new unit types required.

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

One Response to Units for Excel 3 – Editing the units table

  1. Pingback: Units for Excel 4: Scripting dictionaries | 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 )

Google+ photo

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

Connecting to %s