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

 

[Ron Rosenfeld]

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

Here's one way. The Text property of the cell contains that which is being displayed. Using regular expressions returns only the digits that exist after the decimal in the displayed item. Since there can be non-digit characters returned by formatting, it is important to count only the digits, and not any other characters.

Also, although I did not do so, it would be trivial to make this function aware of non-dot decimal symbols. Let me know if you require this.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NumDecDispl(cell_reference)

in some cell.

Note that if cell_reference refers to multiple cells, the function will return an array of the results.

============================================
Option Explicit
Function NumDecDispl(rg As Range) As Variant
Dim v() As Variant, c As Range
Dim re As Object
Const sPat As String = "^[^.]+\.(\d+).*"
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = sPat
.MultiLine = True
End With

If rg.Count = 1 Then
NumDecDispl = Len(re.Replace(rg.Text, "$1"))
Else
ReDim v(0 To rg.Count - 1)
For Each c In rg
v(i) = Len(re.Replace(c.Text, "$1"))
i = i + 1
Next c
NumDecDispl = v
End If
End Function
===============================

 

[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

 

[Ron Rosenfeld]

which assumes that you do not have
"." in text.

Excellent point! And one which I failed to consider in my regex solution.

Fortunately, only requires a change in the regex to take that into account. And while I'm sure there are more efficient regexes for this purpose, this seems to work:

==================================
Option Explicit
Function NumDecDispl(rg As Range) As Variant
Dim v() As Variant, c As Range
Dim re As Object
Const sPat As String = "^.*?\S*\.(\d+).*$"
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = False
.Pattern = sPat
.MultiLine = True
End With

If rg.Count = 1 Then
NumDecDispl = Len(re.Replace(rg.Text, "$1"))
Else
ReDim v(0 To rg.Count - 1)
For Each c In rg
v(i) = Len(re.Replace(c.Text, "$1"))
i = i + 1
Next c
NumDecDispl = v
End If
End Function
=====================================

 

[Ron Rosenfeld]

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

As I mentioned in my reply to joeu2004, who raised the issue of dots within the custom formatting that were not part of the number, I made a change in the regex portion. However, what I posted in response to his is flawed, and the latest iteration of the regex pattern is expressed by:

Const sPat As String = "^.*?\d\.(\d+).*$"

The entire UDF:

============================
Option Explicit
Function NumDecDispl(rg As Range) As Variant
Dim v() As Variant, c As Range
Dim re As Object
Const sPat As String = "^.*?\d\.(\d+).*$"
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = sPat
.MultiLine = True
End With

If rg.Count = 1 Then
NumDecDispl = Len(re.Replace(rg.Text, "$1"))
Else
ReDim v(0 To rg.Count - 1)
For Each c In rg
v(i) = Len(re.Replace(c.Text, "$1"))
i = i + 1
Next c
NumDecDispl = v
End If
End Function
============================

 

[Ron Rosenfeld]

Excellent point! And one which I failed to consider in my regex solution.

Fortunately, only requires a change in the regex to take that into account. And while I'm sure there are more efficient regexes for this purpose, this seems to work:

==================================
Option Explicit
Function NumDecDispl(rg As Range) As Variant
Dim v() As Variant, c As Range
Dim re As Object
Const sPat As String = "^.*?\S*\.(\d+).*$"
Dim i As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = False
.Pattern = sPat
.MultiLine = True
End With

If rg.Count = 1 Then
NumDecDispl = Len(re.Replace(rg.Text, "$1"))
Else
ReDim v(0 To rg.Count - 1)
For Each c In rg
v(i) = Len(re.Replace(c.Text, "$1"))
i = i + 1
Next c
NumDecDispl = v
End If
End Function
=====================================

The regex should be changed to:

^.*?\d\.(\d+).*$

and .Global = True