How to make For loop work with non integers

Question

The following code is easy and outputs as expected

CODE:

Option Explicit

Sub Test_loop2()
Dim i As Long
  For i = -3 To 3 Step 1
    Debug.Print i
  Next i
End Sub

OUTPUT:

The following code is exiting early due to rounding

Option Explicit

Sub Test_loop2()
Dim i As Double
  For i = -0.3 To 0.3 Step 0.1
    Debug.Print i
  Next i
End Sub

OUTPUT:

What is the most reliable method I can use whilst retaining a For Loop to ensure the last value is run in the loop for non integers?

Eg For i = X to Y step Z - Y must always be reached if it's multiple of Z

For i = 0 to 0.3 step 0.1 then 0.3 will be in loop

For i = 0 to 0.3 step 0.2 then 0.3 will NOT be in the loop

Answer 1

Floating point arithmetic will eventually screw you if you use a Double (or Single) as counter.

For counters, stick to whole numbers. Then derive your floating point value from that counter. Example:

Dim i As Long
Dim d As Double
For i = 0 To 6
    d = (i - 3) * 0.1 ' or whatever formula needed
    Debug.Print d
Next i

Answer 2

Another option for Double as loop limits and counter

Sub dTest()

    Dim i As Double

    For i = -0.31 To 0.31 Step 0.1

        Debug.Print Round(i, 1)

    Next

End Sub

---

Result:

-0.3 
-0.2 
-0.1 
 0 
 0.1 
 0.2 
 0.3 

Answer 3

Looks like using decimal instead of double works here

Sub Test_loop2()
Dim i As Variant
Dim ffrom As Variant
Dim fto As Variant
Dim fInc As Variant

    ffrom = CDec(-0.3)
    fto = CDec(0.3)
    fInc = CDec(0.1)

  For i = ffrom To fto Step fInc
    Debug.Print i
  Next i
End Sub

Answer 4

GD,

The problem with using Double (or Float) values in a loop is that both of them are an approximation of a value.

To have a loop end on encountering an absolute, such as:

For i=1 to 5

Would require i to be exactly 5 in order for the loop to work properly, yet the double datatype could be anything from 4.999999999 to 5.000000001 (as example) which would subsequently not satisfy the =5 requirement of the For To loop. You could potentially opt for choosing a

Do 

'do some code here

Loop Until i>5 

where the threshold becomes a bit more vague, which is ideal for float or double datatypes.

Normally it's best practice as per @Jean-François Corbett 's answer to use only 'Integer' or 'Long' datatypes in the counter and step values and use a formula to adjust whatever counting/calculation method you require for your code variable.

Answer 5

I simply added a constant of 0.00001 to the upper limit to avoid the issue of floating point inaccuracy mentioned above, e.g.:

For s = 12 To 19.5 + 0.00001 Step 0.1

The code shows sample values that originally highlighted the problem to me, but apart from the constant 0.00001 these could be variables. If your increment could be less than 0.00001 you might need to fine tune this.

Answer 6

Probably the quickest solution I found was to use the Round operand.

Sub Test_loop2()
Dim i As Double
  For i = -0.3 To 0.3 Step 0.1
    Debug.Print Round(i, 1)
  Next i
End Sub