For...Next vs Do...Loop, and the Winner is?

[ADezii]

If you are executing a code segment for a fixed number of iterations, always use a For...Next Loop instead of a Do...Loop, since it is significantly faster. Each pass through a Do...Loop that iterates a specified number of times, requires you to also implement or decrement some sort of Loop Counter, while a For...Next Loop does that work for you. Both Loops will provide the same results, but the For...Next Loop is substantially faster. One obvious point to mention is that you can't always replace a Do...Loop with a For...Next, you only want to use a For...Next if the number of Loop Iterations is fixed, and not based on some logical condition.

I've created some simple Benchmark Tests involving 3 trials comparing the efficiency of these two Loops using the timeGetTime() API Function. A comparative ratio of the two processes has also been calculated giving you a better viewpoint on the two comparisons. I'll post the code below for the Benchmark Tests. Should you have any questions, please feel free to ask them.

'Declaration needed for API Call
Public Declare Function timeGetTime Lib "winmm.dll" () As Long

Dim lngCounter As Long
Dim lngStart_1 As Long
Dim lngStart_2 As Long
Dim lngStop_1 As Long
Dim lngStop_2 As Long
Dim varTestVariable As Variant
 
Const conNUM_OF_ITERATIONS As Long = 10000000
 
'******************************* 1st up, For...Next *******************************
lngStart_1 = timeGetTime()
 
  For lngCounter = 1 To conNUM_OF_ITERATIONS
    'do some processing here
    varTestVariable = (lngCounter / 0.25) * 1.5
  Next
lngStop_1 = timeGetTime()
 
Debug.Print "For...Next took (" & FormatNumber((lngStop_1 - lngStart_1), 0) & _
            ") milliseconds to execute the expression"
'**********************************************************************************
 
'******************************* 2nd up, Do...Loop ********************************
lngCounter = 1
 
lngStart_2 = timeGetTime()
  Do Until lngCounter > conNUM_OF_ITERATIONS
    'same processing here as for the For...Next Loop
    varTestVariable = (lngCounter / 0.25) * 1.5
      lngCounter = lngCounter + 1
  Loop
lngStop_2 = timeGetTime()
 
Debug.Print "Do...Loop took (" & FormatNumber((lngStop_2 - lngStart_2), 0) & _
            ") milliseconds to execute the expression"
'**********************************************************************************
 
Debug.Print "The For...Next Loop executed the same code in [" & _
            Format((lngStop_1 - lngStart_1) / (lngStop_2 - lngStart_2), "Percent") & _
            "] of the time it took Do...Loop!"

OUTPUT:

For...Next took (766) milliseconds to execute the expression
Do...Loop took (1,515) milliseconds to execute the expression
The For...Next Loop executed the same code in [50.56%] of the time it took Do...Loop!
 
 
For...Next took (781) milliseconds to execute the expression
Do...Loop took (1,516) milliseconds to execute the expression
The For...Next Loop executed the same code in [51.52%] of the time it took Do...Loop!
 
For...Next took (781) milliseconds to execute the expression
Do...Loop took (1,532) milliseconds to execute the expression
The For...Next Loop executed the same code in [50.98%] of the time it took Do...Loop!

NOTE: The Access 2002 Developers Handbook stated that the For...Next Loop runs in about 45% of the time that Do...Loop runs for comparative code. My personal tests based on the above listed code segment, revealed a figure of approximately 51.02%, based on an Average of 3 trial runs.

[Denburt]

Out of curiosity why didn't you mention the following?

While...Wend

I don't have time to set up your function and test it out right now or I would....

[ADezii]

Out of curiosity why didn't you mention the following?

While...Wend

I don't have time to set up your function and test it out right now or I would....

Hello Denburt. I didn't mention While...Wend because I was strictly referring to Looping Structures that can deal with a fixed number of iterations, as opposed to a Looping Structure terminating when a logical condition becomes True or False.