Solved: Timing my macro

[marshybid]

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]

[vba]

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
[/vba]

[grichey]

From http://support.microsoft.com/kb/213481
[VBA]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[/VBA]

[marshybid]

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

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]

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.
[VBA]MsgBox (EndTime - StartTime)[/VBA]

[marshybid]

Thanks Gavin,

I will mark this thread as solved.

Have a good weekend

Marshybid