For Next Loop in Excel VBA Creating More Output Values Than Expected

Question

Tried running a for next loop and it resulted in more values than the value range divided by the step count.

Can anyone tell me why the following VBA code is producing what seems like an endless (I had to stop it because it was going for so long) amount of values, I was expecting it to produce 2626 values in the active cell column i.e 2.626/0.001?

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

Answer 1

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