How to determine visible decimal places

[Guentjo]

In VBA, how can I determine how many decimal places are visible in the activecell?

let's say the true value of the cell is "1.23", but I have it fomatted to "0.0" so what I see is "1.2"

I'd like the code to return a value of "1" because 1 digit is visible to the right of the decimal point.

 

[PA HS Teacher]

How about a User Defined Function?

Function DecimalsShowing(Rng As Range) As Integer
Dim sNum As String, i, iDecimal As Integer
sNum = Format(ActiveCell.Value, ActiveCell.NumberFormat)
DecimalsShowing = 0
iDecimals = 9.9 * 10 ^ 307
For i = 1 To Len(sNum)
  If InStr(1, sNum, ".") Then iDecimal = InStr(1, sNum, ".")
  If i > iDecimal Then DecimalsShowing = i - iDecimal
Next i
End Function

Then in your existing code,

NumberOfDecimalPlaces = DecimalsShowing(ActiveCell)

 

[just_jon]

Or -

************ IGNORE POSTING ****************

 

[PA HS Teacher]

******** Ignore Posting ***********

 

[just_jon]

Jon,
Won't that count all decimal places regardless of whether or not they are displayed? I thougt the OP only wants to know the number of decimal places that are visible?

Yeah, totally mis-read the post ... Bleck! Gotta give up workin', I guess...

 

[Guentjo]

Bleck! Gotta give up workin', I guess...

Oh, please don't have to give up so easily :wink:

The UDF is a neat option. I think I'll go with that, at least for now.

Thanks for the insight and time.

 

[erik.van.geit]

Hi, guys,
perhaps I'm missing something here, at the moment Teacher's code is not giving what I would expect, but i'm sure you tested it ...

would this do ?

Sub test()
nr = ActiveCell.NumberFormat
d = InStr(1, nr, ".")

    If d = 0 Then
    MsgBox "0"
    Else
    MsgBox Len(nr) - d
    End If

End Sub

of course this wouldn't be correct if the numberformat is general or any other non-numeric-format
kind regards,
Erik

 

[Guentjo]

I'm experiencing problems with different formats with both options.

Currency (being on of the formats I'm testing) gives very strange results (different) with both options

With the UDF
$4.40 returns a value of 4 digits
$(4.40) returns a value of 3 digits

With e.v.g.'s code
the return for both $4.40 and $(4.40) is 39

Of course the Currency format is truely:
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

you will notice there are 39 digits to the right of the first decimal point in the above string.

Odd. Similar problems occur with different formats. for the UDF, if the cell value is "1" and formated as "General" the DecimalsShowing = 7. which I think is from the 7 letters in the word general.

 

[erik.van.geit]

what seemed to be rather simple when reading your initial post, turns out to be more complicated
perhaps a closer look will bring a solution, but first I would like to know: what do you need this for ?

 

[erik.van.geit]

could perhaps be enhanced
anyway this is working in most of the cases for me

Sub test()
'still not working when formatted as time or fraction or custom
If Not IsNumeric(ActiveCell) Then
MsgBox "not numeric", 48, "ERROR"
Exit Sub
End If

nr = ActiveCell.NumberFormat
d = InStr(1, nr, ".")

    If d = 0 Then
    nr = ActiveCell
        d = InStr(1, nr, ",")   'US "."
        If d = 0 Then
        MsgBox "0"
        Exit Sub
        End If
    End If
    
    cnt = 0
    For n = d + 1 To Len(nr)
        If Mid(nr, n, 1) = "0" Or Mid(nr, n, 1) = "#" Or IsNumeric(Mid(nr, n, 1)) Then
        cnt = cnt + 1
        Else
        Exit For
        End If
    Next n
    MsgBox cnt
End Sub

still curious about your purpose

best regards,
Erik