vba 在excel中用VBA截断双精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11347704/
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
Truncating Double with VBA in excel
提问by Ehudz
I need to truncate the amount of decimal places of my double value for display in a textbox. How would one achieve this with vba?
我需要截断我的 double 值的小数位数以在文本框中显示。如何使用 vba 实现这一目标？
回答by Siddharth Rout
You can either use ROUNDfor FORMATin VBA
您可以在 VBA 中使用ROUNDforFORMAT
For example to show 2 decimal places
例如显示2位小数
Dval = 1.56789
Debug.Print Round(dVal,2)
Debug.Print Format(dVal,"0.00")
Note: The above will give you 1.57. So if you are looking for 1.56then you can store the Dval in a string and then do this
注：以上给你1.57。因此，如果您正在寻找，1.56那么您可以将 Dval 存储在一个字符串中，然后执行此操作
Dim strVal As String
dVal = 1.56789
strVal = dVal
If InStr(1, strVal, ".") Then
Debug.Print Split(strVal, ".")(0) & "." & Left(Split(strVal, ".")(1), 2)
Else
Debug.Print dVal
End If
回答by Gary McGill
If you want to roundthe value, then you can use the Round function (but be aware that VBA's Round function uses Banker's rounding, also known as round-to-even, where it will round a 5 up or down; to round using traditional rounding, use Format).
如果您想对值进行四舍五入，则可以使用 Round 函数（但请注意，VBA 的 Round 函数使用 Banker 的舍入，也称为舍入到偶数，它会将 5 向上或向下舍入；使用传统的四舍五入四舍五入，使用格式）。
If you want to truncatethe value without rounding, then there's no need to use strings as in the accepted answer - just use math:
如果您想在不舍入的情况下截断值，则无需像接受的答案一样使用字符串 - 只需使用数学：
Dim lDecimalPlaces As Long: lDecimalPlaces = 2
Dim dblValue As Double: dblValue = 2.345
Dim lScale = 10 ^ lDecimalPlaces
Dim dblTruncated As Double: dblTruncated = Fix(dblValue * lScale) / lScale
This yields "2.34".
这产生“2.34”。
回答by Makah
You can use Int() function. Debug.print Int(1.99543)
您可以使用 Int() 函数。 Debug.print Int(1.99543)
Or Better:
或更好：
Public Function Trunc(ByVal value As Double, ByVal num As Integer) As Double
Trunc = Int(value * (10 ^ num)) / (10 ^ num)
End Function
So you can use Trunc(1.99543, 4)==> result: 1.9954
所以你可以使用Trunc(1.99543, 4)==>result: 1.9954
回答by justengel
So fun story. I was messing around with a quick VB conversion function. I just want to truncate a double into an integer.
好有趣的故事。我在搞一个快速的 VB 转换功能。我只想将双精度截断为整数。
value = Int(83.768)
value == 83
Awesome, something in VB actually worked.
太棒了，VB 中的某些东西确实有效。
Oops, I forgot it doesn't work with negative numbers
糟糕，我忘了它不适用于负数
value = Int(-83.768)
value == -84
... yeah that just happened. VB uses Banker rounding.
......是的，刚刚发生的。VB 使用银行家舍入。
Public Function Trunc(ByVal value As Double) As Integer
' Truncate by calling Int on the Absolute value then multiply by the sign of the value.
' Int cannot truncate doubles that are negative
Trunc = (Abs(value) / value) * Int(Abs(value))
End Function
If you want specific decimal places do what Makah did only with Abs around the value so Int can truncate properly.
如果您想要特定的小数位，请执行 Makah 仅对值周围的 Abs 所做的操作，以便 Int 可以正确截断。
Public Function Trunc2(ByVal value As Double, Optional ByVal num As Integer = 1) As Double
' Truncate by calling Int on the Absolute value then multiply by the sign of the value.
' Int cannot truncate doubles that are negative
Dim sign As Integer
sign = Abs(value) / value
Trunc2 = sign * (Int(Abs(value) * (10 ^ num)) / (10 ^ num))
End Function
