excelvbaloops

For Next Loop with Step 0.001 Creating More Output Values Than Expected


This VBA code is producing what seems like an endless amount of values. I had to stop it because it was going for so long.

I expected it to produce 2626 values (2.626/0.001) in the active cell column.

Sub simp3()

    Dim i As Long
    Dim max1 As Long
    Dim max2 As Long
    Dim r1 As Range
    Dim r2 As Range
    Set r1 = Range("AP7:AP26500")
    Set r2 = Range("AS7:AS2633")
    
    Range("AS7").Select
    
    For i = 0 To 2.626 Step 0.001
        Range("C32") = i
        max1 = Application.WorksheetFunction.max(r1)
        ActiveCell.Value = max1
        ActiveCell.Offset(1, 0).Select
    Next i
    
    max2 = Application.WorksheetFunction.max(r2)
    Range("AS3").Value = max2

End Sub

Solution

  • The reason for the behaviour is that implicit conversion is taking place.

    As you defined i as a variable with data type long, operations like 0 + 0.001 will be casted to long and therefore i = i + 0.001 will be 0 again. You have created an endless loop.

    Just use Dim i as double

    https://bettersolutions.com/vba/data-types/converting.htm

    Check this example

       Sub implcitConversions()
        
        Dim i As Long
        i = 1
        i = i + 0.01
        ' Output will be 1 as 1 + 00.1 was converted to long by rounding
        Debug.Print i
        
        i = 1
        ' Output will be 1.01 as in this case
        ' a widening takes place due to the use of debug.print
        Debug.Print i + 0.01
    
    End Sub