Time Calculation - How long a macro takes to run
 

I am trying to add into my macro a bit of code that times how long it takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??

Time Calculation - How long a macro takes to run
 

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??

Time Calculation - How long a macro takes to run
 

Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
format (as the real code I will use will take several minutes to run)

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??

Time Calculation - How long a macro takes to run
 

from the immediate window:

numSeconds = 1021
? format(numSeconds/86400,"hh:mm:ss")
00:17:01

so dividing the number of seconds by 86400 and then using the format command
should work

MsgBox format( timecalc/86400,"hh:mm:ss")

--
Regards,
Tom Ogilvy


"cdb" wrote in message
...
Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
format (as the real code I will use will take several minutes to run)

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue
statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end
time??

Time Calculation - How long a macro takes to run
 

Works a treat - many thanks again for the help.

One thing puzzles me though. Why do you have to divide by 84000? I know this
is the number of seconds in a day, but why do you have to divide the number
of seconds in the timer by the number of seconds in the day to convert to
hh:mm:ss format??

Any ideas?? I'm not in that much of a need to know, was just wondering the
logic behind it.

"Tom Ogilvy" wrote:

from the immediate window:

numSeconds = 1021
? format(numSeconds/86400,"hh:mm:ss")
00:17:01

so dividing the number of seconds by 86400 and then using the format command
should work

MsgBox format( timecalc/86400,"hh:mm:ss")

--
Regards,
Tom Ogilvy


"cdb" wrote in message
...
Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
format (as the real code I will use will take several minutes to run)

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue
statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end
time??

Time Calculation - How long a macro takes to run
 

Time and dates are one format. Excel records dates (and time) as the
elapsed number of days since a base date. so 6 hours would be represented
as 0.25 and 30 hours as 1.25 (one day and 6 hours).

so you have to convert you time to the number of days (thus divide by
24*60*60)

Intrigued? See Chip Pearson's page on this topic:

http://www.cpearson.com/excel/datetime.htm

--
Regards,
Tom Ogilvy



"cdb" wrote in message
...
Works a treat - many thanks again for the help.

One thing puzzles me though. Why do you have to divide by 84000? I know
this
is the number of seconds in a day, but why do you have to divide the
number
of seconds in the timer by the number of seconds in the day to convert to
hh:mm:ss format??

Any ideas?? I'm not in that much of a need to know, was just wondering the
logic behind it.

"Tom Ogilvy" wrote:

from the immediate window:

numSeconds = 1021
? format(numSeconds/86400,"hh:mm:ss")
00:17:01

so dividing the number of seconds by 86400 and then using the format
command
should work

MsgBox format( timecalc/86400,"hh:mm:ss")

--
Regards,
Tom Ogilvy


"cdb" wrote in message
...
Cheers Tom, works a treat. Is there any way in displaying this in
mm:ss
format (as the real code I will use will take several minutes to run)

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative
number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long
it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd
number
(-1.85185185185233E-04). I have tried it without the TimeValue
statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end
time??

Time Calculation - How long a macro takes to run
 

Tom,
This is an interesting thread. I do not understand what causes the timer to
stop except if i reaches 100000000.

What will tell it to stop?

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??

Time Calculation - How long a macro takes to run
 

The timer will stop when it gets to 100000000 only - that is the only
criteria in the below code.

"Papa Jonah" wrote:

Tom,
This is an interesting thread. I do not understand what causes the timer to
stop except if i reaches 100000000.

What will tell it to stop?

"Tom Ogilvy" wrote:

Sub test2()

i = 1

starttime = Timer

While i < 100000000
i = i + 1
Wend

endtime = Timer
timecalc = EndTime - StartTime

MsgBox (starttime & endtime & timecalc)

End Sub

Measures the number of seconds.

(subtracting the endtime from starttime would give a negative number).

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
I am trying to add into my macro a bit of code that times how long it
takes
to execute, but I can't seem to get it right.

As a test I have been using the following code:

Sub test2()

i = 1

starttime = TimeValue(Time)

While i < 100000000
i = i + 1
Wend

endtime = TimeValue(Time)
timecalc = TimeValue(starttime) - TimeValue(endtime)

MsgBox (starttime & endtime & timecalc)

End Sub

But when I output the result (timecalc) it gives a really wierd number
(-1.85185185185233E-04). I have tried it without the TimeValue statement
too
and get the same sort of result.

Is there an easy way of substracting the start time from the end time??