在 VBA 精度问题中比较双精度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/235409/
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
Compare double in VBA precision problem
提问by Eric
I have trouble comparing 2 double in Excel VBA
我在 Excel VBA 中比较 2 double 时遇到问题
suppose that I have the following code
假设我有以下代码
Dim a as double
Dim b as double
a = 0.15
b = 0.01
After a few manipulations on b, b is now equal to 0.6
对 b 进行几次操作后，b 现在等于 0.6
however the imprecision related to the double data type gives me headache because
然而，与双数据类型相关的不精确性让我头疼，因为
if a = b then
//this will never trigger
end if
Do you know how I can remove the trailing imprecision on the double type?
你知道我如何消除双精度型的尾随不精确吗？
回答by Rob Walker
You can't compare floating point values for equality. See this article on "Comparing floating point numbers" for a discussion of how to handle the intrinsic error.
您不能比较浮点值是否相等。有关如何处理内在错误的讨论，请参阅有关“比较浮点数”的这篇文章。
It isn't as simple as comparing to a constant error margin unless you know for sure what the absolute range of the floats is beforehand.
除非您事先确定浮点数的绝对范围是多少，否则它不像与恒定误差范围进行比较那么简单。
回答by Anonymous Type
if you are going to do this....
如果你打算这样做......
Dim a as double
Dim b as double
a = 0.15
b = 0.01
you need to add the round function in your IF statement like this...
您需要像这样在 IF 语句中添加舍入函数...
If Round(a,2) = Round(b,2) Then
//code inside block will now trigger.
End If
See also here for additional Microsoft reference.
另请参阅此处以获取其他 Microsoft 参考。
回答by user3706920
Here is a simple function I wrote:
这是我写的一个简单的函数：
Function dblCheckTheSame(number1 As Double, number2 As Double, Optional Digits As Integer = 12) As Boolean
If (number1 - number2) ^ 2 < (10 ^ -Digits) ^ 2 Then
dblCheckTheSame = True
Else
dblCheckTheSame = False
End If
End Function
Call it with:
调用它：
MsgBox dblCheckTheSame(1.2345, 1.23456789)
MsgBox dblCheckTheSame(1.2345, 1.23456789, 4)
MsgBox dblCheckTheSame(1.2345678900001, 1.2345678900002)
MsgBox dblCheckTheSame(1.2345678900001, 1.2345678900002, 14)
回答by Toon Krijthe
It is never wise to compare doubles on equality.
在平等上比较双打永远是不明智的。
Some decimal values map to several floating point representations. So one 0.6 is not always equal to the other 0.6.
一些十进制值映射到几个浮点表示。所以一个 0.6 并不总是等于另一个 0.6。
If we subtract one from the other, we probably get something like 0.00000000051.
如果我们从另一个中减去一个，我们可能会得到类似 0.00000000051 的结果。
We can now define equality as having a difference smaller that a certain error margin.
我们现在可以将相等定义为差值小于某个误差范围。
回答by C. Dragon 76
As has been pointed out, many decimal numbers cannot be represented precisely as traditional floating-point types. Depending on the nature of your problem space, you may be better off using the Decimal VBA type which can represent decimal numbers (base 10) with perfect precision up to a certain decimal point. This is often done for representing money for example where 2-digit decimal precision is often desired.
正如已经指出的那样，许多十进制数不能精确地表示为传统的浮点类型。根据您的问题空间的性质，您最好使用 Decimal VBA 类型，它可以表示十进制数（基数为 10），精确到某个小数点。这通常用于表示货币，例如通常需要 2 位小数精度的地方。
Dim a as Decimal
Dim b as Decimal
a = 0.15
b = 0.01
回答by DJ.
The Currency data type may be a good alternative. It handles relatively large numbers with fixed four digit precision.
Currency 数据类型可能是一个不错的选择。它以固定的四位精度处理相对较大的数字。
回答by Josh Anstead
Work-a-round?? Not sure if this will answer all scenarios, but I ran into a problem comparing rounded double values in VBA. When I compared to numbers that appeared to be identical after rounding, VBA would trigger false in an if-then compare statement. My fix was to run two conversions, first double to string, then string to double, and then do the compare.
周而复始？？不确定这是否能回答所有情况，但我在比较 VBA 中的四舍五入双精度值时遇到了问题。当我与四舍五入后似乎相同的数字进行比较时，VBA 会在 if-then 比较语句中触发 false。我的解决方法是运行两次转换，首先将双精度转换为字符串，然后将字符串转换为双精度，然后进行比较。
Simulated ExampleI did not record the exact numbers that caused the error mentioned in this post, and the amounts in my example do not trigger the problem currently and are intended to represent the type of issue.
模拟示例我没有记录导致本文中提到的错误的确切数字，我的示例中的金额目前不会触发问题，旨在代表问题的类型。
Sub Test_Rounded_Numbers()
Dim Num1 As Double
Dim Num2 As Double
Let Num1 = 123.123456789
Let Num2 = 123.123467891
Let Num1 = Round(Num1, 4) '123.1235
Let Num2 = Round(Num2, 4) '123.1235
If Num1 = Num2 Then
MsgBox "Correct Match, " & Num1 & " does equal " & Num2
Else
MsgBox "Inccorrect Match, " & Num1 & " does not equal " & Num2
End If
'Here it would say that "Inccorrect Match, 123.1235 does not equal 123.1235."
End Sub
Sub Fixed_Double_Value_Type_Compare_Issue()
Dim Num1 As Double
Dim Num2 As Double
Let Num1 = 123.123456789
Let Num2 = 123.123467891
Let Num1 = Round(Num1, 4) '123.1235
Let Num2 = Round(Num2, 4) '123.1235
'Add CDbl(CStr(Double_Value))
'By doing this step the numbers
'would trigger if they matched
'100% of the time
If CDbl(CStr(Num1)) = CDbl(CStr(Num2)) Then
MsgBox "Correct Match"
Else
MsgBox "Inccorrect Match"
End If
'Now it says Here it would say that "Correct Match, 123.1235 does equal 123.1235."
End Sub
回答by Elimar
Try to use Single values if possible. Conversion to Double values generates random errors.
如果可能，尝试使用 Single 值。转换为 Double 值会产生随机错误。
Public Sub Test()
Dim D01 As Double
Dim D02 As Double
Dim S01 As Single
Dim S02 As Single
S01 = 45.678 / 12
S02 = 45.678
D01 = S01
D02 = S02
Debug.Print S01 * 12
Debug.Print S02
Debug.Print D01 * 12
Debug.Print D02
End Sub
45,678
45,678
45,67799949646
45,6780014038086
