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

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, "")

Else

' --- if the value is zero, well, return zero...

str = "0"

End If

CEngNotation = str

Exit Function

error_hander:

' --- this is really just for debugging suspected problems

Resume Next

End Function

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, "")

Else

' --- if the value is zero, well, return zero...

str = "0"

End If

CEngNotation = str

Exit Function

error_hander:

' --- 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

Loop

End Sub

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.

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

Cheers,

Scott