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

[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

 

[isabelle]

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

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.

 

[N Lee]

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

 

[joeu2004]

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

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

 

[N Lee]

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

 

[eboomer]

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:

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