Monday, February 13, 2012

Engineering Notation in VBA/VB6

I do a lot of work using MS Excel and VBA.  To help me quickly judge magnitudes of values in standard SI units, I like to see my measurement values listed in "Engineering Notation".  That is to say a customized for of Scientific notation where exponents are a multiples of 3 (etc. ... -9 (nano), -6 (micro), -3 (milli), 0 (unit), 3 (kilo), 6 (mega), etc...).  I have found it impossible to use the VB6 Format$() function to achieve this.   So, here is a short little function to convert a double floating-point number to an "Engineering Notation" string.

For the uninitiated, the format string that works in an Excel custom cell format, does not work the same way in VB.  Specifically, using the VB function, Format$(0.001, "##.0.0E+0") does not yield the same output as formatting the cell: Custom > ##.0.0E+0.  The cell format is what we want... it just can't seem to replicate this output using the Format$() function in VB  (You'd think it would.)  But, I assure you, it does not!

Years ago, when faced with this perplexing behavior, I wrote a similar function that used a series of If/ElseIf/Else statements; it is long and kind of kludgy, but it works.  However, this time around I wanted something more compact that would not need an If condition for each range of 10^(k3) numbers. 

In this most recent attempt, I've made use of logarithm functions to make one that is much shorter (in lines-of-code) as well as more extensible.  As is, this will cover a very wide range of values.  It is pretty much limited by the precision of the OS and/or CPU floating point processors.    I am sure there are even more efficient ways to code this, but I think in addition to being fairly compact (for VB) this code is very easy to understand.  The only caveat is know that the VB Log() is the natural log (a.k.a. Ln() or LN() or "log-base-e")  Not log-base-10 (a.k.a. log())).  So "log-base-e to log-base-10 conversion" is needed.

Anyhow, I am posting this more for my own future reference than anything else.  Based on my own Google search on the subject, I did not find any solution to suite my needs.  So, here's my attempt to fill the gap.
Public Function CEngNotation(doubleValue As Double) As String
    Dim x As Double    ' --- Original Double (Floating-point)
    Dim y As Double    ' --- Mantissa
    Dim n As Long      ' --- Exponent
    Dim str As String
    Dim sign As String
'On Error GoTo error_hander   ' --- uncomment for debug; disable when bug-free!
    x = doubleValue
    If x <> 0 Then
        If x < 0 Then
            ' --- x *must* be positive for log function to work
            x = x * -1
            sign = "-"    ' --- we need to preserve the sign for output string
        End If
        n = 3 * CLng((Log(x) / Log(1000)))   ' --- calculate Exponent...
                                                           '     (Converts: log-base-e to log-base-10)
        y = x / (10 ^ n)                     ' --- calculate Mantissa.
        If y < 1 Then                        ' --- if Mantissa <1 then...
            n = n - 3                        ' --- ...adjust Exponent and...
            y = x / (10 ^ n)                 ' --- ...recalculate Mantissa.
        End If
        ' --- Create output string (special treatment when Exponent of zero; don't append "e")
        str = sign & y & IIf(n <> 0, "e" & IIf(n > 0, "+", "") & n, "")
        ' --- if the value is zero, well, return zero...
        str = "0"
    End If
    CEngNotation = str
    Exit Function
    ' --- this is really just for debugging suspected problems
    Resume Next
End Function

And here's a function that I used to test my CEngNotation() function:

Private Sub Test_CEngNotation()
    Dim x As Double
    x = 10500300
    Do While x > 0.000000001
        Debug.Print x, CEngNotation(x)
        x = x / 10
End Sub

1 comment:

  1. Thanks! This is nice. I would point out a typo in the excel format you posted, should be ##0.0E+0. But I ran into the same issue a while back.

    Not sure if it's actually shorter, but I had done this with a Mod function. I wrote my own Mod (or you could overload the operator) so it could deal with decimals and the output was always positive as with the Worksheet function MOD(). The pseudocode is as follows:
    MyMod (As Double) = Log10(Abs(value)) Mod 3
    MyMod value from [0 to 1) means there is only 1 digit before the decimal (vba format code "0.0##E+0")
    from [1 to 2) means you want 2 digits before the decimal (vba format code "00.0#E+0")
    from [2 to 3) means you want 3 digits before the decimal (vba format code "000.0E+0")

    In my case, I didn't need to deal with zero, and log10(val) = log(val)/log(10).
    You can also leverage this to make all your engineering units have fixed precision as I showed here, which is nice.