1

I have a variable, Diff as a double. (values are a sample from my Workbook)

Dim Diff As Double
Sheets(Sheet1).Activate
Diff = 4382.98-4117.34-265.64

Values for calculating Diff come from cells formatted as numbers. I then use Diff as an argument in an if statement.

If Diff <> 0 Then
    ActiveSheet.Range(A2).Value = 265.64 + Diff
End If

Diff should equal 0, but the if statement is proceeding as if the condition is true. I have similar if statements that do not have problems like this. Do I need to format my values differently?

Edit: Had 43892.98 instead of 4382.98 Edit2: Had 256.64 instead of 265.64

Community
  • 1
  • 1
J. Mill
  • 25
  • 4
  • 4
    [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Jacob H May 30 '18 at 20:21
  • 1
    Your Diff also does not equal 0 here, floating point math issues aside. – Jacob H May 30 '18 at 20:24
  • I tried rounding Diff 'Round(43892.98-4117.34-256.64+0.000001,2)' and still have the error (the extra 0.0000001 is to avoid Banker's rounding). – J. Mill May 30 '18 at 20:25
  • Should be 4382.98, not 43892.98. – J. Mill May 30 '18 at 20:27
  • Yeah, that equals `8.99999999999943`. Not zero. – RBarryYoung May 30 '18 at 20:41
  • Wow, I'm having issues typing. It should also be 265.64, not 256.64. I've learned a valuable lesson in proofreading. – J. Mill May 30 '18 at 20:43
  • And that also is not zero, it is `-5.6843418860808E-13`. Try adding a `Debug.Print Diff` statement right before your IF statement. – RBarryYoung May 30 '18 at 20:49
  • To make a very long story short, try following the link that Jacob H posted and reading that. The bottom line is, unless you keep everything in integer values, ***you should never test floating-point values for equality!*** – RBarryYoung May 30 '18 at 20:51

2 Answers2

0

I stored Diff As Currency instead of Double and the issue cleared up.

J. Mill
  • 25
  • 4
0

  1. Never, NEVER, NE-VER compare doubles on equity! (vars of floating point type)
    Let even just in the mind only - never...
    And you do right - your compare is "<>" - and you got the right result.

  2. Currency is INTEGER type, i.e. not-floating point var.

    • values, exceeded 4 digits after point, are rounding internally;
    • compare on equity is correct.
      .
user6698332
  • 407
  • 3
  • 14