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?

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
--
HTH...

Jim Thomlinson


"John" wrote:

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?

Hi John,

John wrote:
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]

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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"John" wrote:

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?

John wrote:
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

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.

--
Regards,
Tom Ogilvy




"John" wrote:

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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"John" wrote:

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?

interesting, thanks one and all for the replies

"witek" wrote:

John wrote:
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