Compare double in VBA precision problem

Question

I have trouble comparing 2 double in Excel VBA

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

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?

Solution 1

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.

Solution 2

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 Round(a,2) = Round(b,2) Then   
     //code inside block will now trigger.
  End If  

See also here for additional Microsoft reference.

Solution 3

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.

If we subtract one from the other, we probably get something like 0.00000000051.

We can now define equality as having a difference smaller that a certain error margin.

Solution 4

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)

Solution 5

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.

Dim a as Decimal
Dim b as Decimal
a = 0.15
b = 0.01

View more solutions