1

I am using VBA to check if the values of two variables are equal, using the "<>" operator. I run that operation for several pairs of values in several pairs of columns. The values are ALL formatted as numbers and the variables are of type "double".

For most of the value pairs it works fine, however for several pairs (corresponding to two columns) there is an issue:

For some of the values the operator treats equal values as unequal. When I debug, I can see that the operator says 71,192 <> 71,192 and the operator treats these values as unequal.

The other value pairs in their corresponding columns work fine but I cannot see any differences between them and the ones that are not working.

Any help would be highly appreciated.

Jochen

jokken
  • 43
  • 1
  • 9
  • possible duplicate of [Why is 134.605\*100 not equal 13460.5 in VBA Access?](http://stackoverflow.com/questions/22322822/why-is-134-605100-not-equal-13460-5-in-vba-access) –  Aug 14 '14 at 08:36
  • actually this : http://stackoverflow.com/questions/235409/compare-double-in-vba-precision-problem –  Aug 14 '14 at 08:36
  • Sorry for the double post and thanks a lot for the links. – jokken Aug 14 '14 at 09:14

1 Answers1

2

As @mehow pointed out, you need to be careful when using floating-point-numbers.
I strongly recommend that you read What Every Computer Scientist Should Know About Floating-Point Arithmetic

If you are using base 10 numbers, the Decimal type may be better suited for your needs. You can convert a Float/Double (base 2 precision) to a Decimal with CDec()

Sub test()
    Dim a As Double, b As Double
    a = 71.192
    b = 7119.2

    Debug.Print a * 100 = b ' False
    Debug.Print CDec(a * 100) = CDec(b) ' True
End Sub
z̫͋
  • 1,531
  • 10
  • 15