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.
Expand|Select|Wrap|Line Numbers
- 'Declaration needed for API Call
- Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Expand|Select|Wrap|Line Numbers
- 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!"
Expand|Select|Wrap|Line Numbers
- 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!