Time to complete code/function

[Guest]

Is there a way for VBA to capture how long it takes for a piece of code or
user defined function to run? Like how fast it is on calculating a simple
UDF?

 

[Guest]

try something like this...

Sub test()
Dim lng As Long
Dim dblStartTime As Double
Dim dblEndTime As Double

dblStartTime = Timer
For lng = 0 To 100000000
Next lng
dblEndTime = Timer
MsgBox "Duration " & dblEndTime - dblStartTime

End Sub

 

[Jake Marx]

Hi John,

Is there a way for VBA to capture how long it takes for a piece of
code or user defined function to run? Like how fast it is on
calculating a simple UDF?

You can use the Timer function:

Dim sngTimer As Single

sngTimer = Timer

'/ your code

Debug.Print Timer - sngTimer

The resolution isn't great on the Timer function, so you may get 0 for UDFs
that don't take long to execute.

For better results, you could try the GetTickCount API function (resolution
of about 10ms):

http://www.vbforums.com/showthread.php?t=231183

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

 

[Guest]

thanks Jim I will play with this... although my first thought is... I wonder
if adding this code to code makes it slower... it must... but I can add new
code and still see the difference.

 

[witek]

Is there a way for VBA to capture how long it takes for a piece of code or
user defined function to run? Like how fast it is on calculating a simple
UDF?

Declare Function GetTickCount Lib "kernel32" () As Long


Dim longStart As Long, longStop As Long
longStart = GetTickCount
......
longStop = GetTickCount

 

[Guest]

first, Timer is a single, so you might want to stick with declaring your
timer related variables as single.

from the immediate window (for confirmation)
? typename(timer)
Single

Second, Jim only put that loop in there as a demonstration of how to time
some sample code. You would replace that loop portion with the working
portion of your current code - the commands you want to time.

If you understood that, then my apologies - but there are many responses
that come back to suggestions such as this that indicate they don't.

 

[Guest]

interesting, thanks one and all for the replies

Declare Function GetTickCount Lib "kernel32" () As Long


Dim longStart As Long, longStop As Long
longStart = GetTickCount
......
longStop = GetTickCount

 

[Guest]

Thanks for clarifying. Somehow I always assumed that Timer returned a double.
You learn something new every day.

As for the idea that using the timer function will skew the result because
the timer itself must take some time to run, you don't have to worry about
that. The resolution of the timer is not great enough to capture the amount
of time it uses up so the results will not be skewed. In order to get an
acurate picture of how long your UDF takes to complete you will probably have
to throw it into a loop (similar to the one I presented but for fewer
itterations) to even get the timer to return any kind of number. My
explanation however could have been better as Tom pointed out.