how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

Try

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer
'Your code here
EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"
End Sub

hope this helps
Rowan

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

Hi Geena,

A rough and ready method:

Sub Tester()
Dim t As Double
Dim ElapsedTime As Double
Dim myTotal As Double

Dim i As Long

For i = 1 To 20 ' To average results

t = Timer
'Your code
ElapsedTime = Timer - t

myTotal = myTotal + ElapsedTime

Debug.Print ElapsedTime, i
Next

Debug.Print "Average time = ", myTotal / i

End Sub


---
Regards,
Norman



"geena" wrote in message
...
how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

guys..i hv a problem
rowan's way at first work ok..then as i keep running the procedure for same
case many times to test the duration of each time it run, the duration keeps
increasing..
first time it was only 5 seconds..i test it for 5 times..
now it is 11 seconds for the 5th time..
why does this happen?how to rectify this?
any idea?
thanks
"geena" wrote:

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

Hi Geena,

You do not show how you run Rowan's code repeatedly, but from the problem
you report, I would guess that you have adapted Rowan's code something like:

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
Dim i As Long
StartTime = Timer
For i = 1 To 10 <<== Loop inserted here
'Your code here
EndTime = Timer
Debug.Print "Time taken: " & EndTime - StartTime & " seconds"
Next i
End Sub

If your amended code looks like this, you can resolve the encounterd problem
by moving the loop initiation line:

For i = 1 To 10

above the line:

StartTime = Timer

This way, the timer will be reinitialised at each loop pass.

If this is not the problem, post back with the adaptation of Rowans's code
that you used.

I presume that you did not experience a similar problem with my suggested
code ( which is completely analagous to Rowan's), simply because I included
the repeat loop structue in my code.




---
Regards,
Norman



"geena" wrote in message
...
guys..i hv a problem
rowan's way at first work ok..then as i keep running the procedure for
same
case many times to test the duration of each time it run, the duration
keeps
increasing..
first time it was only 5 seconds..i test it for 5 times..
now it is 11 seconds for the 5th time..
why does this happen?how to rectify this?
any idea?
thanks
"geena" wrote:

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

Hi Geena,

Just to add (and to avoid any confusion), for my testing purposes, I
changed:

MsgBox "Time taken: " & EndTime - StartTime & " seconds"

in Rowan's code to:

Debug.Print "Time taken: " & EndTime - StartTime & " seconds"

This was purely a matter of personal convenience, and by all means change
the line back.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Geena,

You do not show how you run Rowan's code repeatedly, but from the problem
you report, I would guess that you have adapted Rowan's code something
like:

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
Dim i As Long
StartTime = Timer
For i = 1 To 10 <<== Loop inserted here
'Your code here
EndTime = Timer
Debug.Print "Time taken: " & EndTime - StartTime & " seconds"
Next i
End Sub

If your amended code looks like this, you can resolve the encounterd
problem by moving the loop initiation line:

For i = 1 To 10

above the line:

StartTime = Timer

This way, the timer will be reinitialised at each loop pass.

If this is not the problem, post back with the adaptation of Rowans's code
that you used.

I presume that you did not experience a similar problem with my suggested
code ( which is completely analagous to Rowan's), simply because I
included the repeat loop structue in my code.




---
Regards,
Norman



"geena" wrote in message
...
guys..i hv a problem
rowan's way at first work ok..then as i keep running the procedure for
same
case many times to test the duration of each time it run, the duration
keeps
increasing..
first time it was only 5 seconds..i test it for 5 times..
now it is 11 seconds for the 5th time..
why does this happen?how to rectify this?
any idea?
thanks
"geena" wrote:

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

hi norman and rowan,
the way i write it was

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer

'My codes here

EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"
End Sub

exactly like Rowan suggests and without any loop.
my code was very long because i link it with other program.

the problem was when i run the same sub procedure many times, the time keep
increasing. i mean i run it once, it was 3 seconds. then i run the same macro
again, it was 5 seconds.
i run it the 5th time it was 11 seconds.i kept on running the same thing it
increases to 20 seconds

when i close the workbook, and open it again and run..it was 3 seconds
again.if i keep closing and reopen and rerun..it is always 3-4 seconds
duration, which is right.
but if i rerun many times in arow, each time it will be increasing in time
duration.why?
so do i need to close the workbook first everytime i want to run it for the
second time to get the correct Sub procedure duration?

i tried your way as well norman but it increase the execution time because
of the loop, i think..also may i know why do we need the loop when we define
things as the 'timer' already? is it possible not to have the loop the way
rowan did, but without having to close the book and reopen everything i want
to rerun my test?thanks guys!!i really appreciate your help!
-geena-





"Norman Jones" wrote:

Hi Geena,

Just to add (and to avoid any confusion), for my testing purposes, I
changed:

MsgBox "Time taken: " & EndTime - StartTime & " seconds"

in Rowan's code to:

Debug.Print "Time taken: " & EndTime - StartTime & " seconds"

This was purely a matter of personal convenience, and by all means change
the line back.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Geena,

You do not show how you run Rowan's code repeatedly, but from the problem
you report, I would guess that you have adapted Rowan's code something
like:

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
Dim i As Long
StartTime = Timer
For i = 1 To 10 <<== Loop inserted here
'Your code here
EndTime = Timer
Debug.Print "Time taken: " & EndTime - StartTime & " seconds"
Next i
End Sub

If your amended code looks like this, you can resolve the encounterd
problem by moving the loop initiation line:

For i = 1 To 10

above the line:

StartTime = Timer

This way, the timer will be reinitialised at each loop pass.

If this is not the problem, post back with the adaptation of Rowans's code
that you used.

I presume that you did not experience a similar problem with my suggested
code ( which is completely analagous to Rowan's), simply because I
included the repeat loop structue in my code.




---
Regards,
Norman



"geena" wrote in message
...
guys..i hv a problem
rowan's way at first work ok..then as i keep running the procedure for
same
case many times to test the duration of each time it run, the duration
keeps
increasing..
first time it was only 5 seconds..i test it for 5 times..
now it is 11 seconds for the 5th time..
why does this happen?how to rectify this?
any idea?
thanks
"geena" wrote:

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro) runs?
I want to be able to tell users how long does each execution take for
different test cases (simulation time) after each run.
Thanks

Sounds like the timer is OK, but the procudure that is being timed runs
longer on subsequent iterations.


I would suspect that whatever you are doing in your macro is causing some
type of accumulation in excel and causing the sub to run slower. You would
have to experiment and find out what is causing this.

--
Regards,
Tom Ogilvy


"geena" wrote in message
...
hi norman and rowan,
the way i write it was

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
StartTime = Timer

'My codes here

EndTime = Timer
MsgBox "Time taken: " & EndTime - StartTime & " seconds"
End Sub

exactly like Rowan suggests and without any loop.
my code was very long because i link it with other program.

the problem was when i run the same sub procedure many times, the time
keep
increasing. i mean i run it once, it was 3 seconds. then i run the same
macro
again, it was 5 seconds.
i run it the 5th time it was 11 seconds.i kept on running the same thing
it
increases to 20 seconds

when i close the workbook, and open it again and run..it was 3 seconds
again.if i keep closing and reopen and rerun..it is always 3-4 seconds
duration, which is right.
but if i rerun many times in arow, each time it will be increasing in time
duration.why?
so do i need to close the workbook first everytime i want to run it for
the
second time to get the correct Sub procedure duration?

i tried your way as well norman but it increase the execution time because
of the loop, i think..also may i know why do we need the loop when we
define
things as the 'timer' already? is it possible not to have the loop the way
rowan did, but without having to close the book and reopen everything i
want
to rerun my test?thanks guys!!i really appreciate your help!
-geena-





"Norman Jones" wrote:

Hi Geena,

Just to add (and to avoid any confusion), for my testing purposes, I
changed:

MsgBox "Time taken: " & EndTime - StartTime & " seconds"

in Rowan's code to:

Debug.Print "Time taken: " & EndTime - StartTime & " seconds"

This was purely a matter of personal convenience, and by all means
change
the line back.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Geena,

You do not show how you run Rowan's code repeatedly, but from the
problem
you report, I would guess that you have adapted Rowan's code something
like:

Sub MsgTimer()
Dim StartTime As Single
Dim EndTime As Single
Dim i As Long
StartTime = Timer
For i = 1 To 10 <<== Loop inserted here
'Your code here
EndTime = Timer
Debug.Print "Time taken: " & EndTime - StartTime & " seconds"
Next i
End Sub

If your amended code looks like this, you can resolve the encounterd
problem by moving the loop initiation line:

For i = 1 To 10

above the line:

StartTime = Timer

This way, the timer will be reinitialised at each loop pass.

If this is not the problem, post back with the adaptation of Rowans's
code
that you used.

I presume that you did not experience a similar problem with my
suggested
code ( which is completely analagous to Rowan's), simply because I
included the repeat loop structue in my code.




---
Regards,
Norman



"geena" wrote in message
...
guys..i hv a problem
rowan's way at first work ok..then as i keep running the procedure
for
same
case many times to test the duration of each time it run, the
duration
keeps
increasing..
first time it was only 5 seconds..i test it for 5 times..
now it is 11 seconds for the 5th time..
why does this happen?how to rectify this?
any idea?
thanks
"geena" wrote:

guys..thanks so much!!it works perfectly..
you guys are great!
-geena-

"geena" wrote:

how do i write in vba to know how long a Sub procedure (macro)
runs?
I want to be able to tell users how long does each execution take
for
different test cases (simulation time) after each run.
Thanks