Floating Point Error in For/Next Loop Counter

[Greg Truby]

Well, I'm glad this just happed to occur in something where it wasn't mission critical...

Try this:

Sub foo()
    For p = -1 To 1 Step 0.2
        With Cells(1, (p + 1) * 5 + 1)
            .NumberFormat = "0%_);[Red](0%);0%"
            .Value = p
        End With
    Next p
End Sub

• What do you get in F1? I'm getting -5.55111512312578E-15%
• What do you get in K1? I'm getting nothing: the loop does not execute for p=1

This is obviously a floating point error, but I hadn't ever thought about it happening on a loop counter.

 

[SydneyGeek]

Number sizes can make a difference. Try...

Sub FP_Formats()
    Dim sngX As Single
    Dim dblY As Double
    Dim decZ As Variant
    
    sngX = 0
    dblY = 0
    decZ = CDec(0)
    
    For i = 1 To 10000
        sngX = sngX + 0.0001: dblY = dblY + 0.0001: decZ = decZ + 0.0001
    Next i
    
    MsgBox "Single: " & sngX & vbCrLf _
        & "Double: " & dblY & vbCrLf _
        & "Decimal: " & decZ
End Sub

Denis

 

[Greg Truby]

Interesting... Currency is the same as Decimal, correct:

Sub FP_Formats()
    Dim sngX As Single
    Dim dblY As Double
    Dim decZ As Variant, i%, crrA As Currency
 
    sngX = 0
    dblY = 0
    decZ = CDec(0)
    crrA = CCur(0)
 
    For i = 1 To 10000
        sngX = sngX + 0.0001: dblY = dblY + 0.0001: decZ = decZ + 0.0001: crrA = crrA + 0.0001
    Next i
 
    MsgBox "Single: " & sngX & vbCrLf _
        & "Double: " & dblY & vbCrLf _
        & "Decimal: " & decZ & vbCr _
        & "Currency: " & crrA
End Sub

Do you get the same results as I do when you run the code I posted?

 

[SydneyGeek]

Yes on both counts. I got the same results as you, and Currency should also give the correct values because it's decimal instead of binary.

Denis