For Next bug ????

[Nick]

In Excel 2002 I have written the following code

Private Sub Test()
Dim i As Double

For i = 1 To 1.4 Step 0.2
Debug.Print i
Next
End Sub

Producing the following text in the debug window
1
1.2

I would expect
1
1.2
1.4

By changing the code to:

Private Sub Test()
Dim i As Double

For i = 1 To 1.6 Step 0.2
Debug.Print i
Next
End Sub

I get what I would expect:
1
1.2
1.4
1.6

Does anyone know why???? I also get this in Excel 2000

 

[Rob van Gelder]

Probably a rounding thing. i is probably close to 1.4 but not quite. With
fpp, it's approximations but it's good enough for most situations.
Suggest you write something like

Dim i as Long

For i = 0 to 2
Debug.Print i * 0.2 + 1
Next

 

[Chip Pearson]

Nick,

The number 1.4 cannot be expressed *exactly* in binary floating
point numbers; only an approximation can be expressed. The
number 1.4 is actually stored as something like
1.4000000000000001. (See www.cpearson.com/excel/rounding.htm for
more information). Therefore, in the third iteration of the loop,
i is actually 1.400000000000001, and the For loop terminates.
Try rewriting your loop with an upper limit with more precision:

For i = 1 To 1.401 Step 0.2
Debug.Print i
Next i


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks chip, I thought that I was going mad!!!

-----Original Message-----
Nick,

The number 1.4 cannot be expressed *exactly* in binary floating
point numbers; only an approximation can be expressed. The
number 1.4 is actually stored as something like
1.4000000000000001. (See

www.cpearson.com/excel/rounding.htm for