I'm trying to figure out how long my macro is taking to run, and I want it as
accurate as possible... it would be great if I could get it down to
millisecond precision, is this possible?

Something like:

function test()
startTime = Now()

....
(function code)
....

endTime = Now()

timeTaken = endTime - startTime
test = timeTaken
end function

You can get sickening accuracy.

The Time() function returns the current time.

If you do timeTaken = endTime - startTime, you get a result returned that
goes way past milliseconds.
--
Thanks,

Bruce Bolio


"Dave" wrote:

I'm trying to figure out how long my macro is taking to run, and I want it as
accurate as possible... it would be great if I could get it down to
millisecond precision, is this possible?

Something like:

function test()
startTime = Now()

...
(function code)
...

endTime = Now()

timeTaken = endTime - startTime
test = timeTaken
end function

I believe time is acurate to a timer tick in a PC and is in days. You have
to multiply by 3600 (days to seconds) and then by 1000 to get millisconds. I
added some dummy execution statements to show the accuracy of the timer. i
also declared variiables as double to get greatter precision.

Function Test() As Double

Dim Starttime As Double
Dim EndTime As Double

Starttime = Now()

Set MyRange = ActiveSheet.Range("C1:Z10000")

x = 0
For Each cell In MyRange
cell = 1234

Next cell
EndTime = Now()

timeTaken = EndTime - Starttime
'convert days to milliseconds
Test = timeTaken * 3600# * 1000#
End Function

"Dave" wrote:

I'm trying to figure out how long my macro is taking to run, and I want it as
accurate as possible... it would be great if I could get it down to
millisecond precision, is this possible?

Something like:

function test()
startTime = Now()

...
(function code)
...

endTime = Now()

timeTaken = endTime - startTime
test = timeTaken
end function

I think the last line should be:

Test = timeTaken * 86400# * 1000#


--
Regards
Vergel Adriano


"Joel" wrote:

I believe time is acurate to a timer tick in a PC and is in days. You have
to multiply by 3600 (days to seconds) and then by 1000 to get millisconds. I
added some dummy execution statements to show the accuracy of the timer. i
also declared variiables as double to get greatter precision.

Function Test() As Double

Dim Starttime As Double
Dim EndTime As Double

Starttime = Now()

Set MyRange = ActiveSheet.Range("C1:Z10000")

x = 0
For Each cell In MyRange
cell = 1234

Next cell
EndTime = Now()

timeTaken = EndTime - Starttime
'convert days to milliseconds
Test = timeTaken * 3600# * 1000#
End Function

"Dave" wrote:

I'm trying to figure out how long my macro is taking to run, and I want it as
accurate as possible... it would be great if I could get it down to
millisecond precision, is this possible?

Something like:

function test()
startTime = Now()

...
(function code)
...

endTime = Now()

timeTaken = endTime - startTime
test = timeTaken
end function

Bruce,

Microsoft appears to disagree. They say the resolution of the Timer, now
and Time are about 1 Second. They show other ways to time code:

http://support.microsoft.com/kb/172338
How To Use QueryPerformanceCounter to Time Code

--
Regards,
Tom Ogilvy


"Bruce Bolio" wrote:

You can get sickening accuracy.

The Time() function returns the current time.

If you do timeTaken = endTime - startTime, you get a result returned that
goes way past milliseconds.
--
Thanks,

Bruce Bolio


"Dave" wrote:

I'm trying to figure out how long my macro is taking to run, and I want it as
accurate as possible... it would be great if I could get it down to
millisecond precision, is this possible?

Something like:

function test()
startTime = Now()

...
(function code)
...

endTime = Now()

timeTaken = endTime - startTime
test = timeTaken
end function

Tom,
For what its worth...
I believe that MS support article is not completely correct. The timer function
returns a Single not a Long and it appears the KB author may have used a
Long as the return value for the timer function and mislead himself.
In Ken Getz's VBA Developers Handbook, he states that the timer resolution
is about 1/18 of a second.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Tom Ogilvy"
wrote in message
Bruce,
Microsoft appears to disagree. They say the resolution of the Timer, now
and Time are about 1 Second. They show other ways to time code:
http://support.microsoft.com/kb/172338
How To Use QueryPerformanceCounter to Time Code
--
Regards,
Tom Ogilvy

Hi Jim,

<AFAIK Timer() returns a Double because you can break it down to a decimal
precision of a second. ie:

Sub TimeEvents()
' use to time procedures or functions
Dim StartTime As Date, EndTime As Date
StartTime = Timer

'Run procedure

EndTime = Timer
MsgBox Format(EndTime - StartTime, "0.00")
End Sub

You modify the format here to increase or decrease the number of decimal
places.

Regards,
Garry

Sub TTT()
Dim strType As String
strType = TypeName(Timer)
MsgBox strType
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"GS"
wrote in message
Hi Jim,
<AFAIK Timer() returns a Double because you can break it down to a decimal
precision of a second. ie:

Sub TimeEvents()
' use to time procedures or functions
Dim StartTime As Date, EndTime As Date
StartTime = Timer

'Run procedure

EndTime = Timer
MsgBox Format(EndTime - StartTime, "0.00")
End Sub
You modify the format here to increase or decrease the number of decimal
places.
Regards,
Garry

Thanks for correcting me, Jim!
GS

"Jim Cone" wrote:


Sub TTT()
Dim strType As String
strType = TypeName(Timer)
MsgBox strType
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"GS"
wrote in message
Hi Jim,
<AFAIK Timer() returns a Double because you can break it down to a decimal
precision of a second. ie:

Sub TimeEvents()
' use to time procedures or functions
Dim StartTime As Date, EndTime As Date
StartTime = Timer

'Run procedure

EndTime = Timer
MsgBox Format(EndTime - StartTime, "0.00")
End Sub
You modify the format here to increase or decrease the number of decimal
places.
Regards,
Garry