在excel中使用VBA截断Double

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实现这一目标？

4 个解决方案

#1

You can either use ROUND for FORMAT in VBA

您可以在VBA中使用ROUND for FORMAT

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.56 then 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

#2

If you want to round the 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的舍入，也称为round-to-even，它将向上或向下舍入5;使用传统舍入舍入，使用格式）。

If you want to truncate the 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”。

#3

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）==>结果：1.9954

#4

So fun story. I was messing around with a quick VB conversion function. I just want to truncate a double into an integer.

这么有趣的故事。我正在搞乱VB转换功能。我只想将一个double截断为一个整数。

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使用Banker舍入。

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

#1