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.
