How to retrieve number of visible digits after decimal point in VBA

  • Thread starter Thread starter N Lee
  • Start date Start date
N

N Lee

Greetings:

Not sure if there's a way to do it, but someone might have a good idea. I want to retrieve the number of visible decimals in a cell as an integer.

For instance, a cell has a value of 98.7654321, and displays as 98.8 because its NumberFormat is "#,##0.0_);(#,##0.0)". This should return '1'.

A cell with a value of 87.6543212 would display as 88 if the value is zero.

I'm NOT asking how to format a cell to show a certain number of decimals. I already know how to do that by setting the NumberFormat. I want to know how to retrieve as an integer the number of decimals that are being displayed.

Thanks,
Nathan
 
hi Nathan,

Sub Macro1()
Dim n As Double, itg As Integer, dcm As Double
n = 98.7654321
itg = Int(n)
dcm = Split(n - itg, ".")(1)
End Sub


--
isabelle



Le 2012-07-13 13:43, N Lee a écrit :
Greetings:

Not sure if there's a way to do it, but someone might have a good idea. I want to retrieve the number of visible decimals
Click to expand...

in a cell as an integer.
For instance, a cell has a value of 98.7654321, and displays as 98.8 because its NumberFormat is "#,##0.0_);(#,##0.0)".
Click to expand...

This should return '1'.
A cell with a value of 87.6543212 would display as 88 if the value is zero.

I'm NOT asking how to format a cell to show a certain number of decimals. I already know how to do that by setting the NumberFormat.
Click to expand...

I want to know how to retrieve as an integer the number of decimals that are being displayed.
 
Thanks for the response, but it's not quite the answer to my question. Thatwill tell me how many decimal places are in a Double, but I want to find how many decimal places are DISPLAYED in a cell. All of my cell VALUES are doubles, but they may be displayed with 0, 2, or 4 decimal places.

Even so, it's a clever bit of code. I think I'm going to make myself a custom function with that which could come in handy for a future project.

Nathan
 
N Lee said:
I want to retrieve the number of visible decimals
in a cell as an integer.
For instance, a cell has a value of 98.7654321, and
displays as 98.8 because its NumberFormat is
"#,##0.0_);(#,##0.0)". This should return '1'.
Click to expand...

Range("A1").Text returns to displayed contents of a cell. So if A1 contains
=PI() with format Number with 2 decimal places, Range("A1").Text returns
"3.14".

If we only had to deal the Number format, the following algorithm would
return the number of decimal places in variable n:

Dim s As String, n As Long
s = Range("a1").Text
n = InStr(s, ".")
If n > 0 Then n = Len(s) - n

However, that will not work with your format and negative numbers. The
following is one algorithm (untested), which assumes that you do not have
"." in text. Someone else might provide a regular expression solution,
which would be better.

n = InStr(s, ".")
If n > 0 Then
For p = n+1 To Len(s)
If Not IsNumeric(Mid(s, p, 1)) Then Exit For
Next
n = p - n -1
End If
 
Very clever plan. I'll get to trying it out on Monday. Thanks for the in-depth description. I hope it will help me solve this little puzzle.

Nathan
 
The above only gives the number of displayed decimal places of a value that is already entered into a cell. This doesn't help if you want to know what the number of decimal places per the cell formatting, if a value hasn't yet been entered (which is a case I needed to handle). For that, you need to do something like:

Code: 
Public Function DecPlaces(myRange As Range) As Integer
    Dim s As String, n As Integer
    If myRange.Areas.Count = 1 Then
        s = myRange.NumberFormat
        n = InStr(s, ".")
        If n = 0 Then
            DecPlaces = 0
        Else
            For p = n + 1 To Len(s)
                If Mid(s, p, 1) <> "0" And Mid(s, p, 1) <> "#" Then Exit For
            Next p
            DecPlaces = p - n - 1
        End If
    End If
End Function
 
Back
Top