0

I have the following in cell data/formula:

A1: 60
A2: 8000
A3: 4000
A4: =COS(A1*PI()/180)*A2/1000

I am trying to run this very simple code snippet in my project:

Dim x as Double, y as Double

x = Sheet1.Range("A3")
y = Sheet1.Range("A4")
If (x / 1000) = y Then
    z = z + 0.0001
    Debug.Print z
End If

I would expect this comparison to evaluate as true. However, it never does so and thus does not execute my manipulation on z. Both x and y are type Double. The Locals window also tells me these values are 4000 and 4, and when I use Debug.Print after setting x and y it tells me the same thing.

I know this seems like it should be a really easy problem, but I wouldn't post it here if it were working. What am I missing that is causing this logical failure?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
SandPiper
  • 2,816
  • 5
  • 30
  • 52
  • 3
    Use ROUND() or INT() to fix the floating point decimal error: `IF INT(x / 1000) = y Then` – Scott Craner Jan 02 '20 at 14:58
  • Agree with @ScottCraner but I can't reproduce your problem with those values. – BigBen Jan 02 '20 at 14:59
  • Unable to reproduce it for the given value of x and y. – stud3nt Jan 02 '20 at 14:59
  • 1
    `However, it never does so and thus does not execute my manipulation on z` How do you know that? Did you step through the code? Try putting a `MsgbBox "Test"` in side the if/endif? – Siddharth Rout Jan 02 '20 at 15:03
  • @SiddharthRout yes, I put a breakpoint on that z line and it never executes. I tried stepping through it line by line too. – SandPiper Jan 02 '20 at 15:05
  • 1
    Are you getting `x` and `y` from somewhere (the sheet, another calculation perhaps), or do you explicitly have `x = 4000` and `y = 4`? – BigBen Jan 02 '20 at 15:06
  • 2
    `Debug.Print (x / 1000)` before that and see what do you get. Similarly do it for y as well. – Siddharth Rout Jan 02 '20 at 15:06
  • @BigBen, yes, the values are getting read from two different cells in the spreadsheet. – SandPiper Jan 02 '20 at 15:07
  • 2
    Ah well that might be the root cause. What do `Debug.Print x = 4000` and `Debug.Print y = 4` return? – BigBen Jan 02 '20 at 15:07
  • 1
    @manoj0790: No you did not neutralize... https://meta.stackoverflow.com/questions/311406/is-it-wrong-to-use-an-upvote-to-balance-out-a-downvote – Siddharth Rout Jan 02 '20 at 15:10
  • @ScottCraner using `Int((x / 1000)) = y` also didn't work. Neither did CInt – SandPiper Jan 02 '20 at 15:10
  • 1
    @BigBen is right. Your worksheet cell values have decimals. Can you debug.print x and y before the `If`? – Siddharth Rout Jan 02 '20 at 15:11
  • Check your locale variables window please just before you think x and y have these values – JvdV Jan 02 '20 at 15:11
  • @SiddharthRout @JvdV normally those values would have decimals, but in this case 4000 is specified by me and 4 was derived from `8*sin(30)` which evaluates neatly to 4 exactly. – SandPiper Jan 02 '20 at 15:13
  • 2
    `8*sin(30)` evaluates to exactly 4? You sure? I'm not great at math but it evaluates to -0,98803 for me. Could you use `Val(Range.Text)` to get your 4 into x and see if that works? – JvdV Jan 02 '20 at 15:16
  • 1
    @SandPiper is that a typo? `8*sin(30)` does not equal 4 – BigBen Jan 02 '20 at 15:16
  • 1
    Ok - so sin(30)=0.5 in degrees.... but `Sin` uses radians. – BigBen Jan 02 '20 at 15:24
  • @BigBen Yes, it evaluates using radians. I typed the comment in degrees because sin(30 degrees) is more recognizable as one half than sin(.52359 radians). The cell is actually a much longer formula that under the current conditions simplifies down to that. I didn't mean to cause confusion with that. The resulting value is a decimal number though. The value for x is specified by the user, but is normally an integer. It doesn't have to be though. – SandPiper Jan 02 '20 at 15:32
  • Ok gotcha - sorry this has been confusing. I would then use `Int` on `y` as well. Edited the "answer" :) – BigBen Jan 02 '20 at 15:35
  • 2
    Piper, did you try debuging as suggested **many** times above? What do you get when you do `Debug.Print X` and `Debug.Print Y` before the `IF` – Siddharth Rout Jan 02 '20 at 15:39
  • In my experience, doing comparisons on calculated doubles is error prone as suggested by Scott Craner. The simple solution is to always `Round` before comparing. – Brian M Stafford Jan 02 '20 at 15:52
  • 1
    @BrianMStafford, maybe worth pointing out that `Round` uses bankers' rounding. – BigBen Jan 02 '20 at 15:53
  • 1
    Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Storax Jan 02 '20 at 15:58
  • @bigben I'll try to identify more specifics later. I'm a little dismayed by all the hate here though. I agree it looks like it should have a simple answer, but I'm not an idiot. I wouldn't have posted if it was behaving as expected. I feel like I got written off in a hurry here. – SandPiper Jan 02 '20 at 20:01
  • I don't doubt that. I think part of the reaction is that this felt like a wild-goose chase. But most likely it's floating-point error. We really do need a reproducible example. And without your formula that results in `y`, we can't reproduce the behavior. I think we need the *actual* formula, not a simplification of it. – BigBen Jan 02 '20 at 20:01
  • @siddharthroutt yes, I tried debug.print and it just output 4 and 4000 each time. Nothing like 4.00000000001 or something that would make sense on why this logic was failing. – SandPiper Jan 02 '20 at 20:03
  • @SandPiper Just because the watch window, locals window, debug.print, or immediate window doesn't show the imprecision doesn't mean it's not there. The IDE just isn't showing it, but it is there in many cases. As Scott Craner said in the first comment on this post, you somehow need to address the imprecision of doubles. The link provided by @Storax gives a lot of good background information and some solutions. In summary, either use the `epsilon` technique or simply round the numbers before you compare. I've done it both ways, but I usually find rounding to be sufficient. – Brian M Stafford Jan 03 '20 at 22:19
  • 1
    @SandPiper As a side note, I also think it too bad the question was closed. Reproducible example? Try `? .1 + .2` and `? .1 + .2 = 0.3` in the immediate window. – Brian M Stafford Jan 03 '20 at 22:19
  • @BigBen I edited the post to include the formula that one of the variables draws from. If I could get some help reopening the post I would appreciate it. – SandPiper Jan 05 '20 at 14:03
  • 1
    Voted to reopen. – BigBen Jan 05 '20 at 14:26
  • @SiddharthRout post edited. Are you willing to provide a reopen vote? – SandPiper Jan 05 '20 at 14:53
  • Voted to reopen – Siddharth Rout Jan 05 '20 at 16:27
  • 2
    Instead of `Double`, declare it as `Currency` – Siddharth Rout Jan 05 '20 at 16:33
  • https://bettersolutions.com/vba/numbers/currency-data-type.htm. You can also search more in Google for `double vs currency vba` – Siddharth Rout Jan 05 '20 at 16:38
  • Just to prove the point , add `Debug.Print (x / 1000) - y` to the code, before the `If`. I get `-8.88178419700125E-16` – chris neilsen Jan 06 '20 at 08:09

0 Answers0