Why does a for-loop in VBA loop one less time when using bounds in the ten-thousandths place?

Question

While working on a function in VBA, I noticed that sometimes during my tests I would get different results from when I ran the same function with different parameters. I wrote the following sub to try to figure out what was going on.

When the for-loop is executed with bounds in the thousandths place, it loops through the for statements 3 times, as I expect it to. Here, the immediate window generously prints a "3" for me:

Const vStart = 0.001
Const vStep = 0.001
Const vStop = 0.003

Sub LoopThru()
    Dim myVar As Double
    Dim counter As Integer 

    For myVar = vStart To vStop Step vStep
        counter = counter + 1
    Next myVar

    Debug.Print counter
End Sub

When I reduce the bounds and step by a magnitude, however, the for-loop only executes the for statements twice.

Const vStart = 0.0001
Const vStep = 0.0001
Const vStop = 0.0003

Sub LoopThru()
    Dim myVar As Double
    Dim counter As Integer

    For myVar = vStart To vStop Step vStep
        counter = counter + 1
    Next myVar

    Debug.Print counter
End Sub

In my actual project, I'm creating a large 3D array of values as a lookup table. Since the first dimension of the array has to be generated using Solver, I will greatly reduce the application's runtime by creating this lookup table instead of having to recalculate using Solver over and over again while the application is running.

Since it is a 3D array, there are 3 nested for loops, the second of which may range through values from the hundred-thousandths to the tenths place.

Perhaps a do-while loop is more fitting for this situation - in fact I'm rewriting the code now to use a while loop instead. Or a conditional set of for-loops. Either way, I would like to know the best solution for this problem, as well as why there is a difference in the number of loops executed depending on the magnitude of the bounds in these for loops.

What happens if you explicitly declare the constants 'As Double'? I'm thinking casting or rounding from the implicit initialised type to Double in your loop may be tripping-up somewhere. — Eight-Bit Guru, Jan 17 '14 at 21:25
The reason for your troubles with the `For` loop is due to float point precission (or lack of). [See this](http://stackoverflow.com/a/7330887/445425) for an explanation. You will need to use `Long` type for your loop counter. — chris neilsen, Jan 17 '14 at 21:26
@Eight-BitGuru, I tried declaring the constants as `Doubles`, but to no avail. @chris, I had also tried to declare the loop counter (`myVar`) as `Long`, but the `Long` variable type is an integer, so I just got an overflow. The help menu also turned me on to the variable type `vbDecimal`, which I attempted but that didn't work either. Maybe a package is missing? — marmalar, Jan 17 '14 at 21:41
The root of your issue is floating point precission, and the rounding that results from it. You _will_ need to reacst your loop counter to `Long` Changing to a different floating point data type doesn't solve the underlying issue. If you need help with that, add more details to your Q. — chris neilsen, Jan 17 '14 at 21:51

chris neilsen · Accepted Answer · 2014-01-18T07:13:09.797

The reason is the limited precission that can be stored in a floating point variable. For a complete explanation you shoud read the paper "What Every Computer Scientist Should Know About Floating-Point Arithmetic", by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Link to paper

In VBA the default floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number.

You will need to refactor your code to use an integer type fro the loop counter.

To demonstrate one way to convert to integer loop counter, consider this:

Const vStart = 0.0001
Const vStep = 0.0001
Const vStop = 0.0003

Sub LoopThru()
    Dim myVar As Double
    Dim counter As Long

    For counter = 0 To (vStop - vStart) * 10000 Step vStep * 10000
        myVar = counter * vStep + vStart
        Debug.Print "Iteration " & counter, "myVar = " & myVar
    Next
End Sub

How you will need to implement in your wider context will depend on your specific situation.

Since you mention you are looping over arrays, consider using something like

For i = LBound(YourArray, n) to UBound(YourArray, n)

where n = the array dimension to loop

Why does a for-loop in VBA loop one less time when using bounds in the ten-thousandths place?

1 Answers1