Does anyone have a favorite Units Conversion Function written for VBA?

[jforbes]

I have an application where I want to perform a conversion on a unit of measure during an Access Query. This is an example of some SQL of how I would like to use the function:

SELECT 
  [ID]
, [ExchangerType]
, [Capacity]
, [CapacityUnitOfMeasure]
 , convertToCurrentUnits([Capacity], [CapacityUnitOfMeasure]) AS CapacityInCurrentUnits
FROM tblHeatExchangerSizingData

I've started building a function to perform this conversion, but I thought I would ask around since I haven't seen a lot on this subject either on the Net or on Bytes.

I'm primarily concerned with converting from one engineering unit to another. Units conversions like these, but not limited to these or by my lack of Imagination:

• Gallons to Liters
• Liters to Cubic Centimeters
• Cubic Centimeters to Gallons
• Kilowatts to Horsepower
• Horsepower to BTUs
• BTU to Kilowatts
• Fahrenheit to Celsius

I will appreciate any input. Thanks -J

[ADezii]

I am not sure that there is a simple answer to your question but here is something that I came up with. I'm not saying that it is the most efficient solution, but it is a solution nonetheless. Here goes:

1. Create a Table consisting of Unique Unit of Measure Types such as:
   UOM_ID Unit
   1 Yards
   2 Feet
   3 Inches
   4 Fahrenheit
   5 Celsius
2. Create a Link {1 ==> MANY} between [UOM_ID] in tblUOMTypes and [ExchangerType] and [CapacityUnitOfMeasure] in tblHeatExchangeSizingData. The prior two Fields will store LONG Values indicating specific Unit of Measure Types.
3. Create a Public Function (ConvertToCurrentUnits) that will accept three arguments: [Capacity], [CapacityUnitOfMeasure], and [ExchangerType].
4. Create Logic within this Function that will analyze these Arguments and generate the appropriate Results. Again, this resolves the problem using a Bulk Code approach and probably not the most efficient approach. The Code will need to be expanded to allow for future conversions.
5. Now that I have totally confused you, download the Attachment to get a first hand view on how this can be done.

[jforbes]

Thanks ADezii,

You came up with pretty much an identical approach as the one I was using, which makes me feel really a good about it. I know it's not going to be particularly fast and for this application that is fine.

I'm posting my test version incase you or anyone else would like to see what I came up with. This is just the prototype for what I hope to have, but it's neat seeing the similarities:

    ' convertToCurrentUnits
Public Function convertToCurrentUnits(ByRef vValue As Variant, ByRef vFrom As Variant) As Variant
 
    Dim vReturn As Variant
    vReturn = vValue
 
    Select Case vFrom
        ' Power
        Case "BTU"
            Select Case gCurrentPowerUnits
                Case "Kilowatt", "KW"
                    vReturn = vValue * 0.0002928104
                Case "HP"
                    vReturn = vValue * 0.0003926652
            End Select
        Case "Kilowatt", "KW"
            Select Case gCurrentPowerUnits
                Case "BTU"
                    vReturn = vValue * 3415.179
                Case "HP"
                    vReturn = vValue * 1.341022
            End Select
        Case "HP"
            Select Case gCurrentPowerUnits
                Case "BTU"
                    vReturn = vValue * 2546.699
                Case "Kilowatt", "KW"
                    vReturn = vValue * 0.7456999
            End Select
 
        ' Temperature
        Case "Fahrenheit", "F"
            Select Case gCurrentTempUnits
                Case "Celsius", "C"
                    vReturn = (vValue - 32) * (5 / 9)
            End Select
        Case "Celsius", "C"
            Select Case gCurrentTempUnits
                Case "Fahrenheit", "F"
                    vReturn = (vValue * (9 / 5)) + 32
            End Select
 
        ' Flow/Volume
        Case "Gallon", "Gallons", "gpm", "g"
            Select Case gCurrentFlowUnits
                Case "Liter", "Liters", "L"
                    vReturn = vValue * 3.785412
                Case "CC"
                    vReturn = vValue * 3785.412
            End Select
        Case "Liter", "Liters", "L"
            Select Case gCurrentFlowUnits
                Case "gallon", "gallons", "gpm", "g"
                    vReturn = vValue * 0.2641721
                Case "CC"
                    vReturn = vValue * 1000
            End Select
        ' Pressure
        ' Time
        ' Area
        ' Torque
        ' Force
        ' Mass
        ' Acceleration
        ' Distance
    End Select
 
    convertToCurrentUnits = vReturn
 
End Function

[NeoPa]

How about storing :

1. The name of the UoM.
2. The number of units of X^1 this UoM relates to.
3. Type of UoM^2 (Optional).

^1 X is a UoM of the same type, assigned arbitrarily by the designer, which allows correlations between UoMs of the same type. It will typically be one of the UoMs covered by the table but this need not necessarily be the case.
^2 If used, this is only necessary to allow the routine to return a calling error if the types of the two UoMs passed are not the same.
Multiple names for the same UoM can be handled by using multiple, otherwise duplicate, records.

The routine would have a call format of :

ConvertUnit(strFromUoM As String _
          , strToUnit As String _
          , dblUnits As Double) As Double

The logic is simply :

1. Find the records of the two UoMs passed.
2. Check that the [Type] of both records match (Only if third field included).
3. Take the Units figure and multiply it by the number of units of the To unit record.
4. Divide that result by the number of units of the From unit record.
5. Return that result.

[ADezii]

NeoPa:
Hope things are well with you. See you are still coming up with interesting and innovative approaches to these Threads. I would expect nothing less from you. One question I would like to ask you for my own benefit. How would you logic handle conversions that would involve an Expression as opposed to an Operator and Operands? Case in point would be:

Celsius = (5/9 (Fahrenheit - 32))

[Rabbit]

How about a conversion table with the fields FromUOM, ToUOM, ShiftBefore, Proportion, and ShiftAfter.

So some of the data in the table could look like:

FromUOM   ToUOM   ShiftBefore   Proportion   ShiftAfter
F         C       -32           .5555        0    
C         F       0             1.8          32

[NeoPa]

Very well thank you my friend :-)

That's a very good question, and finds a hole in my overly simplistic suggestion.

Nevertheless, I suspect expanding this table by a single [Offset] field, a Double value that is a measurement of the offset between the zero points of the UoM and the arbitrary UoM used, would get around it quite straightforwardly.

So, the approach would be :

1. Find the records of the two UoMs passed.
2. Check that the [Type] of both records match (Only if third field included).
3. Divide the starting figure by the factor of the From unit record.
4. Take that result and add to it the [Offset] of the From unit record.
5. Subtract the [Offset] of the To unit record from the previous result.
6. Multiply that result by the factor of the To unit record.
7. Return that result.

Assuming these records :

UoM       Factor Offset   Type
Celcius       1  273.15   Temperature
Fahrenheit  1.8  255.372  Temperature
Kelvin        1    0      Temperature
ºC            1  273.15   Temperature
ºF          1.8  241.15   Temperature
K             1    0      Temperature

Converting 50ºC to Fahrenheit (ºF) (and back again) thus goes :
50ºC -> 323.15 -> 323.15 -> 67.778 -> 122ºF.
122ºF -> 67.778 -> 323.15 -> 323.15 -> 50ºC.

[jforbes]

Reading the suggestions and feedback really helped me think this through. So thank you all.

Because of all the thinking, I decided to try out storing off the Formulas in a conversion definitions table and then using the Eval() function along with the Replace() function to see how well it would work. And it seems to be working quite well.

It's getting late, so I'll give it a stress test tomorrow, but I wanted to post back the code for more critique and review, plus I thought is was some interesting code:

    ' convertToCurrentUnits
Public Function convertToCurrentUnits(ByRef vValue As Variant, ByRef vFromUnit As Variant) As Variant
    On Error GoTo ErrorOut
 
    Dim vReturn As Variant
    vReturn = vValue
 
    Static gCurrentFromUnit As String
    Static gCurrentFormula As String
 
    Dim sToUnit As String
    Dim sFormula As String
 
    ' Look up new Formula if From Units have changed, or Formula is empty.
    ' Example of F to C formula: ({From}-32) * (5/9)
    If vFromUnit <> gCurrentFromUnit Or gCurrentFormula = "" Then
        Select Case DFirst("UofMType", "tblUofMConversion", "FromUofM='" & vFromUnit & "'")
            Case "Power"
                sToUnit = gCurrentPowerUnits
            Case "Flow"
                sToUnit = gCurrentFlowUnits
            Case "Temp"
                sToUnit = gCurrentTempUnits
            Case "Pressure"
                sToUnit = gCurrentPressureUnits
            Case "Area"
                sToUnit = gCurrentAreaUnits
            Case "Torque"
                sToUnit = gCurrentTorqueUnits
            Case "Force"
                sToUnit = gCurrentForceUnits
            Case "Mass"
                sToUnit = gCurrentMassUnits
            Case "Acceleration"
                sToUnit = gCurrentAccelerationUnits
            Case "Distance"
                sToUnit = gCurrentDistanceUnits
            Case Else
                sToUnit = ""
        End Select
 
        gCurrentFormula = DFirst("Formula", "tblUofMConversion", "FromUofM='" & vFromUnit & "' AND ToUofM='" & sToUnit & "'")
        gCurrentFromUnit = vFromUnit
    End If
 
    ' place From Value into the formula and Evaluate it.
    sFormula = Replace(gCurrentFormula, "{From}", vValue)
    vReturn = Eval(sFormula)
 
ExitOut:
    convertToCurrentUnits = vReturn
   Exit Function
 
ErrorOut:
    gErrorMessage = ""
    Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)
    Resume ExitOut
End Function

Thanks again, I really appreciate all the help.

[MikeTheBike]

Hi All

If you don't want to write a VBA function there is the possibility of using a calculated field in a query (faster that a VBA function for more than a few records!). To achieve this you would need a table like this

    Units       Factor           a           b
    F -> C      0.5556           459.67      273.15
    C -> F      1.8              273.15      459.67
    HP -> kW    0.746              0           0

The calculated field would take the form
([UniteToConvert] + [a])*[Factor] - [b]

In the case of Horse power to kW etc this effectively
[UniteToConvert]*[Factor] as [a] and [b] are zero.

This only required a reference to one record for the conversion.

All you need to do is define a relationship between the units be be converted and the convertion record !?

Not sure if this too relevant to the actual requirements, but it is interesting!

MTB

[ADezii]

I accidentally came across this Thread again and a light bulb went on in my head, a scary thought to say the least! Why not let Excel do all the hard work via Automation Code in Access with its Convert() Function which converts a Number from one Measurement System to another. You don't have to worry about Formulas, Conversion Tables, Offsets, Conversion Factors, etc. You simply need a small amount of Code along with the knowledge of what Arguments to pass to the Function. I'll throw it out there and graciously welcome any comments either Pro or Con. Here it goes:

1. Create a Global Variable to represent the Excel Application Object in a Standard Code Module.
   

   Public appExcel As Excel.Application

2. Create a simple Function that performs the conversion encapsulating Excel's Convert() Function within.
   

   Public Function fConvertUnits(sngValue As Single, strFrom As String, strTo As String) As Variant
   On Error GoTo Err_fConvertUnits
    
   fConvertUnits = appExcel.WorksheetFunction.Convert(sngValue, strFrom, strTo)
    
   Exit_fConvertUnits:
     Exit Function
    
   Err_fConvertUnits:
     fConvertUnits = "N/A"
       Resume Exit_fConvertUnits
   End Function

3. Create an Instance of Excel, NOT within the Function, then Call this Function with various Arguments passed to it.
   

   'Create a Single Instance of the Excel Application and use it
   'for all subsequent Function Calls
   Set appExcel = New Excel.Application
    
   'Convert 100 Degrees Celsius to Fahrenheit ==> produces 212
   Debug.Print fConvertUnits(100, "C", "F")
    
   'Convert 212 Degrees Fahrenheit to Celsius ==> produces 100
   Debug.Print fConvertUnits(212, "F", "C")
    
   'Convert 250 Degrees Fahrenheit to Kelvin ==> produces 394.261111111111
   Debug.Print fConvertUnits(250, "F", "K")
    
   'Convert 400 Degrees Kelvin to a fictitious Unit of Measurement ==> produces N/A
   Debug.Print fConvertUnits(250, "K", "HELP")
    
   MsgBox "Demo finished"

4. Display the OUTPUT.
   

   212 
   100 
   394.261111111111 
   N/A

5. At some point Quit the Instance of Excel and deallocate any Resources assigned to this Instance.
   

   appExcel.Quit
   set appExcel = Nothing

[jforbes]

I haven't spelled this question out fully as I was trying to keep things simple, but it seems like it is no longer simple. =)

The utility that I'm creating is partially for Hydraulics Systems design and sizing, with some electronics and pneumatics design thrown in for good measure (measure, haha). The idea behind all this is that the user would specify on the fly what units they want to work in and the Worksheets, Selector Forms, Configurators and Calculators would adjust and display everything they are working on in their units of choice. For the Calculator and Worksheet style Forms, this wasn't much of a problem and it was addressed primarily in code.

What started this Bytes Question was the first of many Selection Forms to be created. Where the User enters in some ballpark parameters which would Filter a Datagrid of Vendor Data, which the user could reorder based on what was most important for the day, like Price, Lead Time or Type.

The Vendor Data is from quite a few global Vendors. So the units of measure vary considerably. In the previous example of Heat Exchangers, there are about 6000 rows and there are three points of data that we are concerned with (right now) for Selection.

• Capacity - The amount of heat transferred (currently three different units of measure)
• Temperature Delta - between the two fluids exchanging heat (two units of measure)
• Flow - rates of the two different fluids in and out of the Heat Exchanger (four different units of measure times two measurements)

I could have just added multiple columns for all the different units and used a query to blown out all the data so there is a measurement for every unit and then display the whole mess to the user (thirteen columns) and be done with it, or even hide and show columns based on what the user has selected as their preferred units. It would probably run faster.

But my hope is to use the same mechanism where the User can select which units of measures they want to work in and just those units are displayed. And more importantly are the units that are used in the Filter/Selection process. Providing this will be transparent to them (except in the case of a long conversion time) and will allow me to address all these conversions through Queries which I will be creating regardless, instead of a surprisingly lot of code.


MikeTheBike,
I appreciate the input and that approach will certainly work for a lot of situations. And it's possible I will need to utilize something of this nature if the response times of the previous test code runs too slow. I'm betting it would speed things up at the cost of flexibility. I've been working on a different project for the last couple days, so I haven't had a chance to work on this to see if speed is an issue. I hope to get back to it in a day or two. So we will see.


ADezii,
You gotta love those Eureka moments. The ones that wake you up out of sleep are the best.

Excel's Convert() was what I looked at right before posting this Question. It seems to work quite well, and it's pretty peppy. I ruled it out though because a few conversions that I know I will eventually want aren't supported. A big one is converting Pressure in Bar to PSI and back again. But, I think if anyone else looking to do something like this they should definitely give this approach some serious thought.



Again, thanks for all the input. It is greatly appreciated.


On a side note, I had my own Eureka moment of loading the Conversion table into an Array during the Database Startup or first use to help speed things up. I just haven't tried it yet.

[ADezii]

loading the Conversion table into an Array during the Database Startup or first use to help speed things up

You may wish to look into the GetRows() Method should you take this approach. The GetRows method copies Records from a Recordset into a two-dimensional Array. The first subscript identifies the field and the second identifies the row number.

[NeoPa]

I doubt the performance would be very noticeable for any of these suggestions, assuming you implement them remotely well. I can't imagine you'd do anything but that so I wouldn't worry about response times on that, not unless you're dealing with many thousands of rows at a time. That wasn't my understanding of your situation at least.

[jforbes]

Thanks to everyone for all the help on this. It turned out well.

• I thought I would share the results for anyone that is interested, so I've attached an Example Database.
• I ended up using an Array to cache the table with all the Unit of Measure conversion info as it did speed things up a little and it was a fun exercise. I attempted to use GetRows() Method as suggested but had a bit of trouble implementing it, so I switched to opening a recordset and then loading it into an Array. During that process I realized what I was doing wrong with the GetRows() method, but I just continued on as the end result was pretty much the same.
• In the Example Database I created a stripped down Selector Form to show what the ultimate goal was to be. It can be Sorted and Filtered by the Converted values. When it is all said an done, it doesn't look like much, but it will save us a lot of time with the added benefit of allowing us to make some better decisions.