Elapsed time to run code
 

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart. That's
accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

you'll never get reliable timings like that


for "reasonable" timers use s't like:

dim lTime&
lTime = application.timer *1000
'code
lTime = -lTime+application.timer *1000
messagebox ltime & "msecs"

I believe that application.timer ticks each 35 or 55 milliseconds
(or sometihng)

So for benchmarking you;ll need to run your codes a few times
(or a few 1000 if your testing functions ;)

For far more reliable timings you'll need API functions.
I always use CStopWatch from KarlPeterson.
http://www.mvps.org/vb/samples.htm






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to
run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart.
That's accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

Otto,

Substitute this statement for "MsgBox Format(Time2 - Time1,
"00:00:00")"

MsgBox Format(Time2 - Time1, "hh:mm:ss")

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart. That's
accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

From help

Returns a Single representing the number of seconds elapsed since midnight.

so you need to divide this number by the number of seconds in a day


MsgBox Format((Time2 - Time1)/86400, "hh:mm:ss")

But see KeepItCool's post on using other methods for timing.

--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart. That's
accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

Otto,

Your Format string is wrong. Change "00:00:00" to "hh:mm:ss".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have the following code to give me the elapsed time for a
macro to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds
apart. That's accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

I appreciate your help. What you have looks much better than my method.
However, I get an error "Object doesn't support this property or method."
with the line:
lTime = application.timer *1000

Also, what does "Dim lTime&" mean? I'm familiar with declarations but not
with the use of the "&" sign in them. Otto
"keepITcool" wrote in message
ft.com...
you'll never get reliable timings like that


for "reasonable" timers use s't like:

dim lTime&
lTime = application.timer *1000
'code
lTime = -lTime+application.timer *1000
messagebox ltime & "msecs"

I believe that application.timer ticks each 35 or 55 milliseconds
(or sometihng)

So for benchmarking you;ll need to run your codes a few times
(or a few 1000 if your testing functions ;)

For far more reliable timings you'll need API functions.
I always use CStopWatch from KarlPeterson.
http://www.mvps.org/vb/samples.htm






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro to
run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart.
That's accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

ouch..

my mistake it should be
vba.timer
timer

also not messagebox but msgbox.

dim lTime& means Dim lTime as long
the & is a so-called TypeDeclaration character.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

I appreciate your help. What you have looks much better than my
method. However, I get an error "Object doesn't support this
property or method." with the line:
lTime = application.timer *1000

Also, what does "Dim lTime&" mean? I'm familiar with declarations
but not with the use of the "&" sign in them. Otto
"keepITcool" wrote in message
ft.com...
you'll never get reliable timings like that


for "reasonable" timers use s't like:

dim lTime&
lTime = application.timer *1000
'code
lTime = -lTime+application.timer *1000
messagebox ltime & "msecs"

I believe that application.timer ticks each 35 or 55 milliseconds
(or sometihng)

So for benchmarking you;ll need to run your codes a few times
(or a few 1000 if your testing functions ;)

For far more reliable timings you'll need API functions.
I always use CStopWatch from KarlPeterson.
http://www.mvps.org/vb/samples.htm






--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro
to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart.
That's accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto

Elapsed time to run code
 

According to this article, VBA.Timer's resolution is 1 second.

http://support.microsoft.com/default...b;en-us;172338
How To Use QueryPerformanceCounter to Time Code


It also provides other methods for higher resolution.

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
ft.com...
ouch..

my mistake it should be
vba.timer
timer

also not messagebox but msgbox.

dim lTime& means Dim lTime as long
the & is a so-called TypeDeclaration character.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

I appreciate your help. What you have looks much better than my
method. However, I get an error "Object doesn't support this
property or method." with the line:
lTime = application.timer *1000

Also, what does "Dim lTime&" mean? I'm familiar with declarations
but not with the use of the "&" sign in them. Otto
"keepITcool" wrote in message
ft.com...
you'll never get reliable timings like that


for "reasonable" timers use s't like:

dim lTime&
lTime = application.timer *1000
'code
lTime = -lTime+application.timer *1000
messagebox ltime & "msecs"

I believe that application.timer ticks each 35 or 55 milliseconds
(or sometihng)

So for benchmarking you;ll need to run your codes a few times
(or a few 1000 if your testing functions ;)

For far more reliable timings you'll need API functions.
I always use CStopWatch from KarlPeterson.
http://www.mvps.org/vb/samples.htm






--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Otto Moehrbach wrote :

Excel 2002, WinXP
I have the following code to give me the elapsed time for a macro
to run.

Time1 = Time
'My code
Time2 = Time
MsgBox Time1 & " " & Time2
MsgBox Format(Time2 - Time1, "00:00:00")

The first MsgBox gives me 2 times that are some 17 seconds apart.
That's accurate.
The second MsgBox shows 00:00:00 no matter what.

What is wrong with the second MsgBox line?

Thanks for all your help. Otto