Greater Than or Equal not working when Equal in VBA

[rgn2000]

The following code was not originally my code and I am not an expert with this stuff, but I found an error in it, and for the life of me, I can't understand why it's not working properly. So I have the actual code set up in a macro.......

Do While Data.Cells(x, 5) <> ""
If CurrentTrend = 1 Then ' CurrentTrend = UP
NewPrice = Data.Cells(x, 3) ' days high
If NewPrice >= CurrentPrice + BlockSize Then ' mark up
Do While NewPrice >= CurrentPrice + BlockSize
ChartRow = ChartRow - 1
ChartP.Cells(ChartRow, ChartCol) = "X"
CurrentPrice = CurrentPrice + BlockSize
Loop

CurrentPrice, NewPrice, and BlockSize are set as Double

In this example, the the data is being pulled correctly.......

CurrentPrice is 87.75 and BlockSize is .05.

It pulls NewPrice which is 87.80.

Now when I hover the mouse pointer in the VBA Editor during a step by step test at each variable, I can clearly see that NewPrice (showing as 87.80) >= CurrentPrice (showing as 87.75) + BlockSize (showing as .05). So then 87.80 is Greater Than Or Equal to 87.80. In this case it is obviously equal. Unfortunately it doesn't go down to the next step which it should. What is most interesting is that if I change the NewPrice.... code to "NewPrice = Data.Cells(x, 3) + .001 ' days high" it totally works. Now doing it this way might work without any issues. I haven't figured that out yet, but that really shouldn't be the answer since it does leave me with a situation in which there could be other errors I that I am not thinking about. For whatever reason, the equal part of the operation seems to be ignored. It's as if I only asked for > than instead of >=. Any ideas?

 

[Micron]

Methinks that while you're looking at values, you're not seeing the true value. That can depend on your data types, especially if you've done calculations against them before hand. You might be seeing 87.80 but the true value might be 87.8021. First I'd try formatting your cell values to 4 decimal places to see if there are any trailing numbers. You could also test by checking in immediate window when code is paused (after code lines with variable values have been executed) such as
?Cstr(CurrentPrice) and hit return
or
?Format(Cstr(CurrentPrice),"#,##0.0000")
EDIT - forgot to say that if you get2 trailing 0's in all tested values your data is likely only 2 decimal places. If you get numbers after 2 decimal places such as 87.8012 then that should be your problem.

 

[rgn2000]

Methinks that while you're looking at values, you're not seeing the true value. That can depend on your data types, especially if you've done calculations against them before hand. You might be seeing 87.80 but the true value might be 87.8021. First I'd try formatting your cell values to 4 decimal places to see if there are any trailing numbers. You could also test by checking in immediate window when code is paused (after code lines with variable values have been executed) such as
?Cstr(CurrentPrice) and hit return
or
?Format(Cstr(CurrentPrice),"#,##0.0000")
EDIT - forgot to say that if you get2 trailing 0's in all tested values your data is likely only 2 decimal places. If you get numbers after 2 decimal places such as 87.8012 then that should be your problem.

The data is definitely the correct number of decimal places. In the occurrence of NewPrice, the data in that cell is 87.80 and not rounded. It's an exact 87.80. As to the other two CurrentPrice and BlockSize they are defined as part of the macro at indeed 87.75 and .05 respectively and exactly with no other decimal points.

 

[Alex Blakenburg]

You might be getting the floating point error. Try using data type currency for the variables instead of double. (Assuming none need more than 4 decimals)

 

[Micron]

I'd agree. There was a similar issue at AccessForums.net where I think CJ_London nailed it as being the reason. I found that using Single data type took care of the problem in my testing.

 

[rgn2000]

I'd agree. There was a similar issue at AccessForums.net where I think CJ_London nailed it as being the reason. I found that using Single data type took care of the problem in my testing.

Thank you so much.....Single took care of it! Very weird system in that Double would be an issue.

 

[Micron]

IIRC, it has something to do with decimal being a floating point type of number and the fact that computer systems are based on binary - but don't quote me on that.