sbNum2Str

If you need a non-scientific number representation with all significant digits and all leading and trailing zeros you can use this function sbNum2Str.
A related function is sbNSig which returns a number with a specified number of significant digits.
Please read my Disclaimer.
1
Function sbNum2Str(d As Double) As String
2
'Returns string with number representation with all
3
'significant digits and leading or trailing zeros, i.e.
4
'1E+3 will be returned as 1000
5
'1E-3 will be 0.001
6
'Pi() will be 3.14159265358979
7
'Reverse("moc.LiborPlus.www") PB 15-Nov-2010 V0.20
8
Dim v
9
Dim lExp As Long, lLenMant As Long
10
Dim sDot As String 'decimal separator
11
Dim sMant As String 'new mantissa
12
​
13
If d < 0# Then
14
    sbNum2Str = "-" & sbNum2Str(-d)
15
    Exit Function
16
End If
17
​
18
sDot = Application.DecimalSeparator
19
​
20
'Split scientific representation into mantissa and exponent
21
v = Split(Format(d, _
22
        "0" & sDot & String(15, "#") & "E+0"), "E")
23
       
24
If Left(v(0), 1) = "0" Then
25
    sbNum2Str = "0"
26
    Exit Function
27
End If
28
​
29
lExp = CLng(v(1))   'get exponent
30
​
31
v = Split(v(0), sDot)
32
​
33
If lExp < 0 Then
34
    sbNum2Str = "0" & sDot & String(-lExp - 1, "0") & _
35
              v(0) & v(1)
36
Else
37
    lLenMant = Len(v(1))
38
    If Len(v(1)) > lExp Then
39
        sMant = v(0) & v(1)
40
        sbNum2Str = Left(sMant, lExp + 1) & sDot & _
41
                  Right(sMant, Len(sMant) - lExp - 1)
42
    Else
43
        sbNum2Str = v(0) & v(1) & String(lExp - lLenMant, "0")
44
    End If
45
End If
46
​
47
End Function
Copied!

sbNSig

sbORB

Last modified 2yr ago

Copy link