通过 Excel VBA 计算小数位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24532464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count decimal places by Excel VBA
提问by Indra
In my excel, there are different types of decimal nos and decimal places are different also.
在我的excel中，有不同类型的小数位数，小数位也不同。
Eg. 112.33, 112.356, 145.1, 25.01, 27.001
例如。112.33、112.356、145.1、25.01、27.001
I need to know the count of cells which has 2 digit decimal places, Eg - 25.01 - that should be one of them. I need this code for excel VBA
我需要知道具有 2 位小数位的单元格计数，例如 - 25.01 - 这应该是其中之一。我需要此代码用于 excel VBA
回答by Alexander Bell
You can use VBA technique as shown in the following example:
您可以使用 VBA 技术，如下例所示：
Dim digit As Integer
Dim num As Double
num = 123.456
digit = Len(CStr(num)) - InStr(CStr(num), ".")
where digitis the number of decimal places.
其中digit是小数位数。
pertinent to your first sample:
与您的第一个样本相关：
digit = Len(CStr(112.33)) - InStr(CStr(112.33), ".")
Rgds,
Rgds，
回答by Rene
Alex's answer can be extended for the case where decimal character (period or comma) is unknown. This may be so if numbers are taken from an Excel sheet and regional settings are unknown in advance. Another extension is the handling of (integer) numbers that lack decimals.
亚历克斯的答案可以扩展到十进制字符（句点或逗号）未知的情况。如果数字取自 Excel 工作表并且事先未知区域设置，则可能会出现这种情况。另一个扩展是处理缺少小数的（整数）数字。
Dim iDigits As Integer
Dim vNumber As Variant
vNumber = Excel.Application.ActiveCell.value
If VBA.IsNumeric(vNumber) And Not VBA.IsEmpty(vNumber) Then
iDigits = Len(vNumber) - Len(VBA.Fix(vNumber))
' correct for decimal character if iDigits > 0
iDigits = IIf(iDigits > 0, iDigits - 1, 0)
Else
iDigits = 0
End If
回答by MatthewHagemann
Function GetNumberDecimalPlaces(theCell As Range) As Integer
Dim periodPlace as integer, stringLength as integer
periodPlace = InStr(1, theCell.Text, ".")
If periodPlace = 0 Then
GetNumberDecimalPlaces = 0
Else
stringLength = Len(theCell.Text)
GetNumberDecimalPlaces = stringLength - periodPlace
End If
End Function
回答by Geoff Griswald
Improving on the very handy function submitted by MatthewHagemann.
改进 MatthewHagemann 提交的非常方便的功能。
This version;
这个版本；
Doesn't error when the user passes more than one cell as a range to the function
Works regardless of whether the number in the cell is stored as Text or as a Number
当用户将多个单元格作为范围传递给函数时不会出错
无论单元格中的数字是存储为文本还是数字都有效
VBA:
VBA：
Function CountDecimalPlaces(InputCell As Range) As Integer
'Counts the number of decimal places in a cell
Dim StringConvert As String
If InputCell.Cells.Count > 1 Then
CountDecimalPlaces = 0
Exit Function
End If
StringConvert = InputCell.Value
If InStr(1, StringConvert, ".") = 0 Then
CountDecimalPlaces = 0
Else
CountDecimalPlaces = Len(StringConvert) - InStr(1, StringConvert, ".")
End If
End Function
