Why is my loop not going through all values?

user_9541614 0
Viewed 61

I'm trying to get a Excel vba macro working. The code I'm using is the following :


Dim foo, bar, toto As Double
Dim nb As Integer
nb = 1

For bar = 1 To 1 Step 0.2
For toto = 1 To 4 Step 0.2
For foo = 1 To 2 Step 0.2
    Cells(nb, 1).Value = CStr("bar " & bar & " toto " & toto & " foo " & foo)
    nb = nb + 1
Next foo
Next toto
Next bar
End Sub

I would expect to see the case toto=4 printed. The end result I'm getting is :

bar 1 toto 1 foo 1
bar 1 toto 1 foo 1.2
bar 1 toto 1 foo 1.4
[....]
bar 1 toto 3.8 foo 1.6
bar 1 toto 3.8 foo 1.8
bar 1 toto 3.8 foo 2

Variable foo is going as expected from 1 to 2 and incrementing by 0.2 each times. On the other hand, variable toto is going from 1 to 3.8 instead of going up to 4.

How can I change my code in order for toto to reach 4 ? I tried changing the upper value of my for loops to (max + 1 step), and for toto it worked but for foo and bar their was an "extra" loop.

I tried to change the code to a while based approach, with no success either.

bar = 1
toto = 1
foo = 1
nb = 1

While (bar <= 1)
toto = 1
    While (toto <= 4)
    foo = 1
        While (foo <= 2)
        
        Cells(nb, 4).Value = CStr("bar " & bar & " toto " & toto & " foo " & foo)
        nb = nb + 1
        foo = foo + 0.2
        Wend
    toto = toto + 0.2
    Wend
bar = bar + 0.2
Wend
for-loop vba excel
2 Answers

This is due to floating-point error. I would avoid using Step 0.2 and instead multiply foo, bar, and toto by 0.2 inside the loop, while only using whole numbers when looping:

Dim foo As Long, bar As Long, toto As Long, nb As Long

nb = 1

For bar = 1 To 5
    For toto = 1 To 20
        For foo = 1 To 10
            Cells(nb, 1).Value = CStr("bar " & bar * 0.2 & " toto " & toto * 0.2 & " foo " & foo * 0.2)
            nb = nb + 1
        Next
    Next
Next

Also note that in vba, Dim foo, bar, toto As Double only declares toto as Double. foo and bar are Variants.

user_92458530

You have declared your variables incorrectly. You need the As statement on every declaration. The way you did this makes foo and bar of type Variant which could be anything that VBA wants at the time. It's best to have one declaration per line. If we make the upper to be slightly greater than 4, like 4.01 then it works. I'm not sure why.

Dim foo As Double
Dim bar As Double 
Dim toto As Double
Dim nb As Integer
nb = 1

For bar = 1 To 1 Step 0.2
    For toto = 1 To 4.01 Step 0.2
        For foo = 1 To 2 Step 0.2
            Cells(nb, 1).Value = CStr("bar " & bar & " toto " & toto & " foo " & foo)
            nb = nb + 1
        Next foo
    Next toto
Next bar

If you don't like adding some slop to your upper limit on toto you can also force significant figures with Round.

This one works the way you would expect:

Sub test()
    Dim foo As Single
    Dim bar As Single
    Dim toto As Single
    Dim nb As Integer
    nb = 1
    
    For bar = 1 To 1 Step 0.2
        For toto = 1 To 4 Step 0.2
            toto = Round(toto, 1)
            For foo = 1 To 2 Step 0.2
                Debug.Print CStr("bar " & bar & " toto " & toto & " foo " & foo)
                nb = nb + 1
            Next foo
        Next toto
    Next bar
End Sub
user_81738700
Related