PDA

View Full Version : Solved: Timing my macro



marshybid
06-06-2008, 08:19 AM
Hi All,

How can add some code to my macro to start a timer when the macro starts and display the total time to complete in a message box when finished.

Thanks,

Marshybid

Bob Phillips
06-06-2008, 08:25 AM
Sub Test()
Dim nTime As Double
Dim i As Long, j As Long

nTime = Timer
For i = 1 To 10000

For j = 1 To 50000

Next j
Next i
MsgBox Timer - nTime & " secs"

End Sub

grichey
06-06-2008, 08:29 AM
From http://support.microsoft.com/kb/213481
Sub ElapsedTime()
Dim StartTime As Double, EndTime As Double

'Stores start time in variable "StartTime"
StartTime = Timer

'Place your code to be timed here

'Stores end time in variable "EndTime"
EndTime = Timer

'Prints execution time in the debug window
Debug.Print "Execution time in seconds: ", EndTime - StartTime
End Sub

marshybid
06-06-2008, 08:38 AM
Thanks to both of you,

xld, you posted



Sub Test()
Dim nTime As Double
Dim i As Long, j As Long

nTime = Timer
For i = 1 To 10000

For j = 1 To 50000

Next j
Next i
MsgBox Timer - nTime & " secs"

End Sub


How does this time the macro that I am running? Where does my macro code go, or do I call this macro as part of the macro I want to time :dunno

Grichey, you posted




Sub ElapsedTime()
Dim StartTime As Double, EndTime As Double

'Stores start time in variable "StartTime"
StartTime = Timer

'Place your code to be timed here

'Stores end time in variable "EndTime"
EndTime = Timer

'Prints execution time in the debug window
Debug.Print "Execution time in seconds: ", EndTime - StartTime
End Sub


Here I know where my mcro goes, but where does the final (elapsed) time show - Debug.Print??? I'm not familiar with this.

Thanks for all of your help,

Marshybid

grichey
06-06-2008, 08:48 AM
ctrl g in the vba editor brings up the immediate window which is where this is shown.

This is for you to see how long it takes; not your users.

If you want it in a box add this where debug.print is.
MsgBox (EndTime - StartTime)

marshybid
06-06-2008, 09:01 AM
Thanks Gavin,

I will mark this thread as solved.

Have a good weekend

Marshybid :hi: