Determining Macro speed

[Luis939]

Interesting question here, Is there anything in VBA or anything else which will tell you the precise speed the macro took to execute. Just wondering so I could compare what algorithms are faster, thanks!

 

[Zathras]

Here is one way:
[blue]

Sub test()
Dim tStart As Double
Dim tElapsed As Long
Dim i As Long
  tStart = Now
  For i = 1 To 27000000
  Next i
  tElapsed = (Now - tStart) * 86400
  MsgBox "Elapsed time = " & tElapsed & " seconds"
End Sub

[/color]
If you think you need more precise measurements, you could use an API call to the the windows tick count, but that would be overkill. If there is less than a second difference between two algorithms, that is probably all you need to determine.

 

[Page410]

Here's some code using the api call that Zathras referenced. This will return your value in milliseconds.

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lngStartTime As Long

Public Sub StartTimer()
'Start the timer, storing the value in
'the module global, lngStartTime.
lngStartTime = timeGetTime()
End Sub

Public Function StopTimer() As Long
' End timing, and return the difference between the
' current time and lngStartTime, measured in milliseconds
StopTimer = timeGetTime() - lngStartTime
End Function

 

[Luis939]

Thanks just out of curiousty how does that work, how does it convert the date and time to a number and why did you multiply it against 86400? thanks!

 

[Zathras]

Date and Time values are floating-point numbers in Excel. Type some dates and times in a worksheet and change the format to General to see.

The unit of measure is Days. To convert from days to seconds you multiply by 24 hrs/day times 60 minutes/hour times 60 seconds/minute. 24 x 60 x 60 = 86,400 seconds per day.

 

[ribhead]

I used this once I don't know if it will help you though.
sub timer_feedback()
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim x As Integer
Dim y As Integer
Dim j As Integer
Dim i As Integer
Dim start, Time_stop As Date
start = Timer

x = 0
y = 0
For i = 1 To 5000
For j = 1 To 1000
a = x + y + i
b = y - x - i
c = x - y - i
Next j
Next i

Time_stop = Timer
MsgBox Format(Time_stop - start, "0.0" & " seconds&quot
End Sub

I may not be very smart but I'm sure wirey!!!!