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.

 

[Guentjo]

Purpose:

I have to appease someone who doesn't like it when a spreadsheet apears to show 1 + 1 = 3, when it's really 1.3+1.3 = 2.6, but I'm not showing any decimals

to that end, I have created a macro that will change the total line in several templates from the standard "=sum()" to a more complex array "{=SUM(ROUND(),#))}".

See rows 4 & 5

Rounding Totals with macro.xls
	A	B	C	D
1	Line1	1.3	1.3
2	Line2	1.4	1.4
3	Line3	1.8	1.8
4	Total	4.5	4.4
5	RoundedTotal	4.5	4.5
Sheet1

I have the code to change the formulas accordingly, which I'm more than happy to share if you want to see it. I adapted an earlier rounding tool. This works great, if the round to digit ("#" from above) is the same across the board, and I could make it a constant. Unfortunately it is not, so I can't. The purpose of this code is to look at the activecell and change the round to digit in the array to the number of visble digits right of the decimal point. So if the cell(which is the sum of a column) has 2 decimal places (as would all the cells in the column) then the array would round to "2". One digit right of the decimal point and the macro would change the array formula to round to 1.

I guess I'm trying to fake "Percision as Displayed" - sort of.

 

[erik.van.geit]

Seems like a workaround which I wouldn't like to use. Instead I would adapt the display ... But perhaps I'm missing something oh! my english and the late hour here (surprised it's almost 3AM )
anyway: did you see my previous post which came with a "solution" ?

 

[PA HS Teacher]

Erik brings up a lot of good points. I took a look at my UDF, and realized I left in an "ActiveCell" where I should have had Range. I fixed that, followed Eriks IsNumeric idea so that the code ignores nonumeric characters. It now appears to work with currency.

Function DecimalsShowing(Rng As Range) As Integer
Dim sNum As String, i, iDecimal, iNonNumbers As Integer
sNum = Format(Rng.Value, Rng.NumberFormat)
DecimalsShowing = 0
iDecimals = 9.9 * 10 ^ 307
iNonNumbers = 0
For i = 1 To Len(sNum)
' If Mid(sNum,1) = "G" then ????
  If InStr(1, sNum, ".") Then
    iDecimal = InStr(1, sNum, ".")
    If i > iDecimal Then
    DecimalsShowing = i - iDecimal
    If Not IsNumeric(Mid(sNum, i, 1)) Then iNonNumbers = iNonNumbers + 1
  End If
  End If
Next i
DecimalsShowing = DecimalsShowing - iNonNumbers
End Function

General Format is still a problem, because the number of characters showing depends on the column width, not the number format.....hmm...

 

[Guentjo]

Seems like a workaround which I wouldn't like to use. .... did you see my previous post which came with a "solution" ?

Absolutely is a work around, but I thought it was a clever one and much better then the alternative of hardcoding something

I haven't had a chance to worrk with your most recent post, I saw it and wanted to explain my situation, then something else came up before I could test.

I'll work with it and let you know. I see the Pennslyvanian has responded again as well, so I'll check that out too.

 

[Guentjo]

General Format is still a problem, because the number of characters showing depends on the column width, not the number format.....hmm...

I agree. The new UDF seems to work with all number formats except general. It even works with the "Text" format.

e.v.g.'s code has the same limitation with the general format and also doesn't work with text format (note: this is an observation only and certainly isn't a requirement for my needs).

I've also seen that both work with limited (at least) custom formats.

I doubt that I'll ever run into cells that have been formatted as 'general' so hopefully that problem isn't really one .

I think I'll go with one of these. Thanks for your help. Very clever solutions.

 

[Guentjo]

e.v.g.'s code has the same limitation with the general format and also doesn't work with text format (note: this is an observation only and certainly isn't a requirement for my needs).

Check that.

I noticed this note in the code:

d = InStr(1, nr, ",")   'US "."

so I changed the comma to a period b/c I'm in the States.

wah la (sp?) both general and text now work

 

[erik.van.geit]

Yep! it's the details which make the difference !!

curious:
I know this
wah la = voilà
but what is (sp?) ?

EDIT: my eldest son just said sp = spelling

 

[Guentjo]

and clearly I butchered the spelling!

 

[erik.van.geit]

You Americans never need French
my place is at 30 kilometers from Wallonie: part of Belgium with French-speaking-people
at 100 km from Germany
at 200 km from England
so we can use some language-knowledge here

 

[Guentjo]

What's a kilometer?