Using Timers to Improve Performance
If you have several alternatives to accomplish the same programming objective, it is sometimes useful to model each method and see the actual time it takes to run. The "Timer" function of Excel provides the number of seconds elapsed since Midnight at the instant the Timer function is executed. By subtracting the beginning timer value from the ending timer value, elapsed time can be measured. This is a very useful way to determine the the most efficient technique to accomplish a mission such as: MATCH vs. VLOOKUP, array sorts vs. worksheet sorts, and hundreds of other examles.
The following simple example shows how to time a loop that occurs 250 million times.
Program Code
Option Explicit
Option Base 1
Sub ExampleOfUsingTimer()
Dim i As Long
Dim j As Long
Dim dteStartTime As Single
Dim dteEndTime As Single
Dim dblSum As Double
dblSum = 0
' ****************************************************************
' Use The Timer Function To Measure the Length Of Time for Loops
' ****************************************************************
dteStartTime = Timer
For i = 1 To 500000
For j = 1 To 500
dblSum = dblSum + 1
Next j
Next i
dteEndTime = Timer
MsgBox ("Run Time = " & dteEndTime - dteStartTime & " Seconds" & vbCrLf & _
"And The Number Of Loops is " & dblSum)
End Sub