Unit conversion for engineering and science

This page discuss about creating Excel application for unit conversion from one unit to another unit based on conversion factors used in engineering and science.
This program uses the worksheets of Excel AddIns as backend database. 49 distinct engineering and science categories are covered. All except temperature are based on the conversion factors available in engineering and science. Temperature unit is calculated to convert to another temperature unit according to formula.
There are other categories which depend on couple of variables in order to convert from one unit to another; for example, concentration, solubility,and gas constant, which are not covered by this page. These may not be well supported by more or less general-purpose userform of this page.

---

Contents

1 User Interface to initiate program run
2 Conversion Factor database
3 Userform Event procedures
4 Computation of unit conversion
5 Update Data Base
6 Download
7 Install Excel AddIns to your machine

1 User Interface to initiate program run

This page uses an existing tab View on the Ribbon, created by RibbonX.
A new group Excel Program is created together with a control labeled Unit Conversion.

CustomUI file is written in XML language as discussed in RibbonX User Interface. CustomUI.xml is found by adding .Zip to the name of Unit_conversion.xlam, Excel AddIns for this page and open the zip file. Press the control's icon to trigger a callback procedure in a standard module. A userform shows up with initial data filled, especially with AddIns worksheet names representing unit categories.

Procedure in a standard module

Procedure Cmb3Populate loops through all the worksheets in Excel Addins Unit_Conversion.xlam.

Option Explicit
Option Compare Text
Sub ConversionStart(control As IRibbonControl)
    With uf_Conversion
        .ComboBox1.Clear: .ComboBox2.Clear
        .ComboBox4.Clear: .ComboBox5.Clear
        .ComboBox3.Clear
        .TextBox1.Value = ""
        .Label6.Caption = ""
        Call Cmb3Populate
        .TextBox1.Value = 1
        .ComboBox3.Value = "Length"
        .TextBox2.Value = 0
        .OptionButton1.Value = True
        .Show
    End With
End Sub

Take note that ThisWorkbook represents the AddIn rather than active workbook. The worksheet names representing unit categories are sorted in the ascending order by a function MySortProcedure before loading to the Combobox3 of the userform. The function MySortProcedure includes bubble sort algorithm.

Sub Cmb3Populate()
    Dim i As Long, SortGroups As Variant
    Application.ScreenUpdating = False
    Set wb = ThisWorkbook    
    UnitCount = wb.Worksheets.Count
    ReDim Preserve UnitGroups(UnitCount)
    For i = 1 To UnitCount
        UnitGroups(i) = wb.Worksheets(i).Name
    Next i
    SortGroups = MysortProcedure(UnitGroups)
    uf_Conversion.ComboBox3.List = SortGroups
End Sub
Private Function MysortProcedure(myList)
     Dim myTemp() As Variant
     Dim anotherIteration As Boolean, Temp As Variant
     Dim i As Single, j As Long, n As Long
     With Application
         .DisplayAlerts = False
         .ScreenUpdating = False
     End With
     For i = LBound(myList) To UBound(myList)
          For j = LBound(myList) To UBound(myList)
              If i <> j And myList(i) = myList(j) Then
                   myList(i) = vbNullString
              End If
          Next j
     Next i
     For i = 1 To UBound(myList)
         If myList(i) <> vbNullString Then
             n = n + 1
             ReDim Preserve myTemp(n)
            myTemp(n) = myList(i)
        End If
    Next i
    n = UBound(myTemp)
    If n > 1 Then
    Do
        anotherIteration = False
        For i = 1 To UBound(myTemp) - 1
            If myTemp(i) > myTemp(i + 1) Then
                Temp = myTemp(i)
                myTemp(i) = myTemp(i + 1)
                myTemp(i + 1) = Temp
                anotherIteration = True
            End If
        Next i
    Loop While anotherIteration = True
    End If
    MysortProcedure = myTemp
End Function

Go to Top

2 Conversion Factor database

As mentioned above, the worksheets of Excel AddIns are used as backend database. To view the conversion factor data, go to Visual Basic Editor, and Click VBAProject (Unit_Conversion.xlam) in the Project Explorer window. Enter password kingstone and click ThisWorkbook. Open Properties Window and set IsAddin as False to view contents of worksheets. Please ensure that the IsAddin property is set back to True after you view.

Go to Top

3 Userform Event procedures

Quite simple and straightforward work flow is programmed.

1. Select category or group name of unit of your interest. 49 categories are designed to be handled. This identifies each individual unit of the category and these are listed in the list down lists of Name of unit and symbol. This is implemented by calling Cbx3Change in a standard module.
2. select a name of unit or a symbol from From row. This enables Definition of the selected unit to appear. This is implemented by calling Cbx1Change in a standard module.cbx1change
3. Enter a value in a From box. Default value is 1.
4. select a name of unit or a symbol from Result row. This enables Definition of the selected unit to appear. This is implemented by calling Cbx1Change in a standard module.cbx1change
5. You can define the number format of computation result by specifying a decimal place, from 0 to 30. YOu can enter a figure directly in the box or pressing spin button up or down. Textbox and spin button is paired. Select either normal number or scientific number format.
6. Computation is initiated only when conditions in the procedure UpdateControls are fulfilled and by calling TBX1change procedure in a standard module.

The procedures pointed to by userform event procedure are kept in a standard module.

3.1 Event Procedure in userform module

Option Explicit
Option Base 1
Private Sub UserForm_Activate()
    With uf_Conversion
        .Top = 185
        .Left = Application.Left + ActiveWindow.Width - _
            .Width - 15
    End With
End Sub
'**********************************
'(Unit Group) change event
'**********************************
Private Sub ComboBox3_AfterUpdate()
    'Unit Group
    Cbx3Change
    UpdateControls
End Sub
Private Sub ComboBox3_Click()
    Cbx3Change
    UpdateControls
End Sub
'*********************************
'(From Unit)  change event
'*********************************
Private Sub ComboBox1_AfterUpdate()
    'Name
    cbx1change
    UpdateControls
End Sub
Private Sub ComboBox1_Click()
    cbx1change
    UpdateControls
End Sub
Private Sub ComboBox4_AfterUpdate()
    'symbol
    cbx4change
    UpdateControls
End Sub
Private Sub ComboBox4_Click()
    cbx4change
    UpdateControls
End Sub
'**********************************
'(To Unit) change event
'**********************************
Private Sub ComboBox2_AfterUpdate()
'Name
    cbx2change
    UpdateControls
End Sub
Private Sub ComboBox2_Click()
    cbx2change
    UpdateControls
End Sub
Private Sub ComboBox5_AfterUpdate()
'Symbol
    cbx5change
    UpdateControls
End Sub
Private Sub ComboBox5_Click()
    cbx5change
    UpdateControls
End Sub
'**********************************
'From Value
'**********************************
Private Sub TextBox1_AfterUpdate()
    UpdateControls
End Sub
Private Sub SpinButton1_Change()
'Enter the decimal place for result's value
    TextBox2.Value = SpinButton1.Value
    UpdateControls
End Sub
Private Sub TextBox2_Change()
    Dim Newval As Long
    Newval = Val(TextBox2.Text)
    If Newval >= SpinButton1.Min And _
       Newval <= SpinButton1.Max Then
        SpinButton1.Value = Newval
    End If
End Sub
Private Sub OptionButton1_Click()
'Choose normal number format
    UpdateControls
End Sub
Private Sub OptionButton2_Click()
'Choose Scientific number format
    UpdateControls
End Sub
'*********************************
'Calculation Control
'*********************************
Private Sub UpdateControls()
    If ComboBox3.Value <> vbNullString And _
       ComboBox1.Value <> vbNullString _
       And ComboBox2.Value <> vbNullString _
       And TextBox1.Text <> vbNullString Then
        Call Tbx1Change
    End If
End Sub

3.2 Procedures in a standard module

 
Sub Cbx3Change()
    Dim i As Long, rs As Long    
    Set ws = wb.Worksheets(uf_Conversion.ComboBox3.Value)
    rs = ws.UsedRange.Rows.Count
    Erase UnitData: Erase CBX1s: Erase CBX4s
    ReDim Preserve UnitData(rs - 1, 4)
    ReDim Preserve CBX1s(rs - 1)
    ReDim Preserve CBX4s(rs - 1)
    UnitData = ws.Cells(2, 1).Resize(rs - 1, 4).Value
    For i = 1 To UBound(UnitData)
        CBX1s(i) = UnitData(i, 1)
        CBX4s(i) = UnitData(i, 2)
    Next i
    With uf_Conversion
        .ComboBox1.Clear: .ComboBox1.Value = ""
        .ComboBox2.Clear: .ComboBox2.Value = ""
        .ComboBox4.Clear: .ComboBox4.Value = ""
        .ComboBox5.Clear: .ComboBox5.Value = ""
        .ComboBox1.List = CBX1s: .ComboBox2.List = CBX1s
        .ComboBox4.List = CBX4s: .ComboBox5.List = CBX4s
        .Label6.Caption = ""
        .Label6.Caption = ""
    End With
End Sub
'******************************************************
'From Unit
'******************************************************
Sub cbx1change()
    With uf_Conversion
        .ComboBox4.Value = .ComboBox4.List(.ComboBox1.ListIndex)
        'Unit definition
        .Label7.Caption = ""
        .Label7.Caption = UnitData(.ComboBox1.ListIndex + 1, 3)
    End With
End Sub
Sub cbx4change()
    With uf_Conversion
        .ComboBox1.Value = .ComboBox1.List(.ComboBox4.ListIndex)
        .Label7.Caption = ""
        .Label7.Caption = UnitData(.ComboBox4.ListIndex + 1, 3)
    End With
End Sub
'******************************************************
'To Unit
'******************************************************
Sub cbx2change()
    With uf_Conversion
        .ComboBox5.Value = .ComboBox5.List(.ComboBox2.ListIndex)
        .Label7.Caption = ""
        .Label7.Caption = UnitData(.ComboBox2.ListIndex + 1, 3)
    End With
End Sub
Sub cbx5change()
    With uf_Conversion
        .ComboBox2.Value = .ComboBox2.List(.ComboBox5.ListIndex)
        .Label7.Caption = ""
        .Label7.Caption = UnitData(.ComboBox5.ListIndex + 1, 3)
    End With
End Sub

Go to Top

4 Computation of unit conversion

The computation does not and does not have to rely on Excel's built-in formula CONVERT. Because we build the conversion factor database, the formula is simple.

Result=(Input figure) * (conversion factor of input unit) / (conversion factor of unit to be computed)

In the meantime, Temperature units are based on the formula related to each other. Input temperature is converted to Kelvin and then the Kelvin is converted to the unit to be computed. The procedures are kept in a standard module. The number format is coded using WorksheetFunction.Rpt.

'*******************************************************
'Data input in the (from) box
'Result in Label6.
'*******************************************************
Sub Tbx1Change()
    Dim ifm As Integer, ito As Integer
    
    With uf_Conversion
        Select Case True
            Case .OptionButton1.Value
                 ResultFormat = _
                    "0." & WorksheetFunction.Rept("0", Val(.TextBox2.Text))
            Case .OptionButton2.Value
                 ResultFormat _
                    = "0." & WorksheetFunction.Rept("0", Val(.TextBox2.Text)) & "E+00"
        End Select
        Select Case .ComboBox3.Value
            Case "Temperature"
                 Call TemperatureConversion
            Case Else
                ifm = .ComboBox1.ListIndex + 1
                ito = .ComboBox2.ListIndex + 1
                .Label6.Caption = _
                Format(Val(.TextBox1.Text) * UnitData(ifm, 4) / UnitData(ito, 4), _
                   ResultFormat)
        End Select
    End With
End Sub
Sub TemperatureConversion()
    Dim txt1 As Double, KelvinDegree As Double
    With uf_Conversion
         'From Unit
         txt1 = Val(.TextBox1.Text)
         Select Case .ComboBox1.Value
              Case "Celsius"
                  KelvinDegree = txt1 + 273.15
              Case "Fahrenheit"
                  KelvinDegree = (txt1 + 459.67) * 5 / 9
              Case "Rankine"
                  KelvinDegree = txt1 * 5 / 9
              Case "kelvin"
                  KelvinDegree = txt1
              Case "Reaumur"
                  KelvinDegree = 5 / 4 * txt1 + 273.15
         End Select
         'Calculate to return to Label6
         Select Case .ComboBox2.Value
              Case "Celsius"
                  .Label6.Caption = Format(KelvinDegree - 273.15, ResultFormat)
              Case "Fahrenheit"
                  .Label6.Caption = Format(KelvinDegree * 9 / 5 - 459.67, ResultFormat)
              Case "Rankine"
                  .Label6.Caption = Format(KelvinDegree * 9 / 5, ResultFormat)
              Case "kelvin"
                  .Label6.Caption = Format(KelvinDegree * 9 / 5, ResultFormat)
              Case "Reaumur"
                  .Label6.Caption = Format(4 / 5 * KelvinDegree - 273.15, ResultFormat)
         End Select
    End With
End Sub

Go to Top

5 Update Data Base

You can update reference data base which are contained in 52 worksheets of unit conversion.xlam, either automatically by creating a procedure or manually through direct access to the data base.
We discuss about manual update here.
Take following steps for manual update:

1. Go to Visual Basic Editor(VBE) to find VBEProject(unit conversion.xlam).
2. Go to Microsoft Objects module to select ThisWorkbook module.
3. Display Properties Window to set IsAddins to False, which will allow you to view and access the contents of the data base worksheets.
4. Go to worksheet and modify the contents.
5. Once you finish your modification, be certain to set IsAddins back to True and save unit conversion.xlam.

Fore more datails, refer to Addins worksheet as backend database.

Go to Top

6. Download the source

The download Excel file is of Excel AddIns with password protected. If you want to refer to code, you need to enter password. The password is kingstone.

Download source code and workbook

7. Install Excel AddIns to your machine

To Install Excel Addins unit conversion.xlam, take the following steps:

1. Unzip download zip file
2. Copy Addins file, Unit conversion.xlam, to folder where you like to kept it.
3. Open an Excel file, new or any existing one.
4. On the Ribbon select File ⇨ Options
5. Click Add-ins on Excel Options panel to display View and manage Microsoft Office Add-ins dialog.
6. Select Excel Add-Ins in the drop down named Manage and click Go to make Add-ins dialog appear.
7. Find Unit conversion in the listdown by scrolling down. If the file is not listed, click Browse to the file you keep to add to the list down.
8. check the checkmark for AddIns file and click OK.
9. The Addins is loaded and an icon is added as discussed in above 1.

For more details,refer to AddIns Install Excel AddIns

Dated on: 21-December-2018