Comparing doubles returns false

Question

I have three numbers from my database and want to compare them in an if statement.

I have a simple conevert function that returns only doubles.

Public Function RetDbl(ByVal obj As Variant) As Double
    On Error Resume Next
    RetDbl = val(Replace(Nz(obj, 0), ",", "."))
End Function

The statement is

If RetDbl(rs.value("NumA")) + RetDbl(rs.value("NumB")) <> (RetDbl(rs.value("NumC")) * 1000) Then
    '[... do some code ...]
End If

With RetDbl(rs.value("NumA")) = 0.33, RetDbl(rs.value("NumB") = 0.5 and RetDbl(rs.value("NumC")) = 0.00083

This always returns false

I also tried:

In the direct field (STRG + G): ?cdbl(0.33) + cdbl(0.50) = cdbl(0.83) returns false. When i leave out the last part it returns 0.83

How can i compare these numbers?

floating point numbers aren't exact values. Hence comparing them with exact values is likely to fail (as you discovered). It is better to check if `|a - b| < epsilon`for some appropriate epsilon — Ronald, Dec 14 '17 at 13:09
I should use this, i guess. But it has worked so far for some days now. Even when i type `?0.33 + 0.50 = 0.83`i only get false. And i never told the program that these were doubles. — Diego, Dec 14 '17 at 13:13
@Ronald is correct. 0.33 + 0.5 = 0.8300000000000001. You can also try rounding or truncation to only compare the significant parts of the floating point numbers. See [this website](http://floating-point-gui.de/) for a lot of good info on how floating point numbers behave in computer programs. — Chris R. Timmons, Dec 14 '17 at 13:16
@Ronald note that the use of a relative epsilon is recommended if you're creating a function to do the comparison — Erik A, Dec 14 '17 at 13:26
@ErikvonAsmuth yes, I know. That's why I wrote "some appropriate epsilon". Usually you have something like 8-12 digits of precision (but that can be much worse, depending on the history; if it's a result of a numerically unstable algorithm, you can end up with anything), hence comparing with `10^-8 * a` could be an appropriate value. Since I don't know anything about the "history" of the number, I'm a bit careful with publishing numbers. — Ronald, Dec 14 '17 at 13:40

Erik A · Accepted Answer · 2017-12-14T14:45:48.640

Comparing floating numbers is hard. Only yesterday, I've posted this question

My solution:

Public Function DblSafeCompare(ByVal Value1 As Variant, ByVal Value2 As Variant) As Boolean
    'Compares two variants, dates and floats are compared at high accuracy
    Const AccuracyLevel As Double = 0.00000001
    'We accept an error of 0.000001% of the value
    Const AccuracyLevelSingle As Single = 0.0001
    'We accept an error of 0.0001 on singles
    If VarType(Value1) <> VarType(Value2) Then Exit Function
    Select Case VarType(Value1)
        Case vbSingle
            DblSafeCompare = Abs(Value1 - Value2) <= (AccuracyLevelSingle * Abs(Value1))
        Case vbDouble
            DblSafeCompare = Abs(Value1 - Value2) <= (AccuracyLevel * Abs(Value1))
        Case vbDate
            DblSafeCompare = Abs(CDbl(Value1) - CDbl(Value2)) <= (AccuracyLevel * Abs(CDbl(Value1)))
        Case vbNull
            DblSafeCompare = True
        Case Else
            DblSafeCompare = Value1 = Value2
    End Select
End Function

Note that the AccuracyLevel (epsilon) could be set to a smaller value, and I'm using the same value for singles and doubles, but it did well for my purposes.

I'm using a relative epsilon, but multiplying it with the first, and not the largest value, since if there's a significant difference the comparison will fail anyway.

Note that I'm using <= and not < since else DblSafeCompare(cdbl(0) ,cdbl(0)) would fail

Note that this function checks for type equality, so comparing integers to longs, doubles to singles, etc. all fails. Comparing Null to Null passes, however.

Implement it:

?DblSafeCompare(cdbl(0.33) + cdbl(0.50) ,cdbl(0.83))
?DblSafeCompare(cdbl(0.331) + cdbl(0.50) ,cdbl(0.83))

Thanks for providing the code. Never thought it had to be that complicated. But now it works like a charm! — Diego, Dec 14 '17 at 13:21
Note that the code I shared was for my use case. As such `DblSafeCompare(Null, Null)` is `True`, and `DblSafeCompare(1, CDbl(1))` is `False` — Erik A, Dec 14 '17 at 13:24

Vityata · Answer 2 · 2017-12-14T16:52:37.127

2

Comparing floating point numbers is really an issue, if you try to do it without understanding of the nature of the floating numbers.

Here is a nice article about it - https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html and How dangerous is it to compare floating point values?

In general, this problem is so big, that some languages like C# have developed a specific class called Decimal which makes comparing run as it would be expected by a non-programmer. Decimal info. In VBA, a similar class is Currency. Thus

CCur(0.33) + CCur(0.50) = CCur(0.83)

Returns True. VBA supports the function CDec, which converts a double to a decimal number, but it does not support the class Decimal. Thus:

CDec(0.33) + CDec(0.50) = CDec(0.83)

would also return True. And is with some better accuracy than Currency. CDec documentation.

edited Dec 14 '17 at 16:52

answered Dec 14 '17 at 13:33

Vityata

42,633
8
55
100

1

For fractions, when using modern VBA applications (can't find the year of introduction), I recommend using the `Decimal` data type, not the `Currency` data type (`CDec` not `CCur`). Decimal is a larger data type, that supports more precise values. Currency is limited to 4 decimals. Decimal is a non-floating number with 29 significant digits. – Erik A Dec 14 '17 at 14:34
@ErikvonAsmuth - unfortunately `Decimal` is not supported by VBA. However, the `CDec` function is quite a good alternative indeed. – Vityata Dec 14 '17 at 16:28
1

Well, not supported might be sort of true. You can't declare a decimal. But you can save one in a variant. See the following example: `Public Sub TestDec(): Dim dec1 As Variant: Dim dec2 As Variant: dec1 = CDec(0.3301111) + CDec(0.5): dec2 = CDec(0.8301111) : Debug.Print dec1 = dec2 : End Sub` This prints `True` in office 2010. You can also store decimals in Microsoft Access. Sorry for the bad formatting – Erik A Dec 14 '17 at 16:38
@ErikvonAsmuth - true. I wanted to edit my comment, but 5 minutes have already passed :) – Vityata Dec 14 '17 at 16:48

Comparing doubles returns false

2 Answers2

Linked