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.
[CODE=vb]
'Declaration needed for API Call
Public Declare Function timeGetTime Lib "winmm.dll" () As Long[/CODE]
[CODE=vb]
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_ITERA TIONS As Long = 10000000
'************** *************** ** 1st up, For...Next *************** *************** *
lngStart_1 = timeGetTime()
For lngCounter = 1 To conNUM_OF_ITERA TIONS
'do some processing here
varTestVariable = (lngCounter / 0.25) * 1.5
Next
lngStop_1 = timeGetTime()
Debug.Print "For...Next took (" & FormatNumber((l ngStop_1 - lngStart_1), 0) & _
") milliseconds to execute the expression"
'************** *************** *************** *************** *************** ********
'************** *************** ** 2nd up, Do...Loop *************** *************** **
lngCounter = 1
lngStart_2 = timeGetTime()
Do Until lngCounter > conNUM_OF_ITERA TIONS
'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((l ngStop_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!"[/CODE]
OUTPUT:
[CODE=text]
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![/CODE]
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.
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.
[CODE=vb]
'Declaration needed for API Call
Public Declare Function timeGetTime Lib "winmm.dll" () As Long[/CODE]
[CODE=vb]
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_ITERA TIONS As Long = 10000000
'************** *************** ** 1st up, For...Next *************** *************** *
lngStart_1 = timeGetTime()
For lngCounter = 1 To conNUM_OF_ITERA TIONS
'do some processing here
varTestVariable = (lngCounter / 0.25) * 1.5
Next
lngStop_1 = timeGetTime()
Debug.Print "For...Next took (" & FormatNumber((l ngStop_1 - lngStart_1), 0) & _
") milliseconds to execute the expression"
'************** *************** *************** *************** *************** ********
'************** *************** ** 2nd up, Do...Loop *************** *************** **
lngCounter = 1
lngStart_2 = timeGetTime()
Do Until lngCounter > conNUM_OF_ITERA TIONS
'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((l ngStop_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!"[/CODE]
OUTPUT:
[CODE=text]
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![/CODE]
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.
Comment