0

I am doing VBA coding in Excel 2007.

I want to add 0.1 when tm is < 5 and add 0.25 when tm is at least 5, and some other cases. In the following code, when in 3rd iteration, the expression (tm < 5) is evaluated true when tm = 5. What is going on?

Even more interesting, if you try starting tm = 4.9, the evaluation would be correct!

    Sub test()

Dim i As Integer
Dim tm As Double
tm = 4.8

For i = 1 To 5

MsgBox tm & " " & (tm < 5)

    If tm < 2 Then
        tm = tm + 0.05
    ElseIf tm < 5 Then
        tm = tm + 0.1
    Else
        tm = tm + 1
    End If


Next i

End Sub
Community
  • 1
  • 1
Jone
  • 1
  • The reason is the limited precision of floating point numbers. [See this answer](http://stackoverflow.com/a/7330887/445425) – chris neilsen May 20 '15 at 09:23
  • possible duplicate of [VBA rounding problem](http://stackoverflow.com/questions/7330665/vba-rounding-problem) – Richard May 20 '15 at 09:25

3 Answers3

0

Floating point values (including VBA's double type) are imprecise.

If you output tm with sufficient decimal places you will find it is a little less than 5.0 (eg. 4.9999999999999) it , but is being rounded for display.

Richard
  • 106,783
  • 21
  • 203
  • 265
0

I think the problem occurs due to data types, double sometimes misrounds numbers due to its precision type, take a look at the case when you use "Currency" instead of "Double" then the condition for 5 is false this could be solution to your problem.

KKowalczyk
  • 333
  • 2
  • 7
0

As stated already it's due to precision of floating point operations. If you want to avoid it round your value before comparison to the desired accuracy, e.g. round(tm,2)<5 instead of just tm<5.

Máté Juhász
  • 2,197
  • 1
  • 19
  • 40