Debugging Timer

A

Adrian T

Hi:


Could anyone recommend a good way to show the speed (in
minutes/seconds) of a sub/function to run?

I am looking for something like this. Function Losses()
takes 1:06 to return a value.


Thank you,
Adrian T
 
M

Michael Malinsky

Try this:

Sub Run_Time()

a = Now()
Put your code here
b = Now()
MsgBox Format((b - a), "hh:mm:ss")

End Sub

The message box will pop up with the amount of time elapsed between b and a.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
C

Chip Pearson

Adrian,

The simplest way is to use the GetTickCount API function, which
returns a value in milliseconds. E.g.,

Public Declare Function GetTickCount Lib "kernel32" () As Long

Sub AAA()

Dim StartTime As Long
Dim EndTime As Long

StartTime = GetTickCount
'
' your code here
'
EndTime = GetTickCount
MsgBox "Procedure took " & Format(EndTime - StartTime, "#,##0") &
" milliseconds"

End Sub



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

Michael Malinsky

Chip,

I'm just wondering the advantage of your way over mine (especially since you
call your way the "simplest).

Thanks.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
 
C

Chip Pearson

Michael,

The primary advantage of using GetTickCount is that it has a
resolution of 1 ms, which can be useful for time tight, fast
loops.


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

Rob van Gelder

I have an example on my website.

Here:

Declare Function timeGetTime Lib "winmm.dll" () As Long

Dim lngStart As Long

Sub Start()
lngStart = timeGetTime()
End Sub

Function Finish()
Finish = timeGetTime() - lngStart
End Function

Sub test()
Dim i As Long, lngLastRow As Long, lngTemp As Long, rng As Range

With ActiveSheet

'set up test data
For i = 1 To 10000: .Cells(i, 1).Value = i: Next

'do the 3 speed tests
Start
For i = 1 To 10000
lngTemp = Range("A" & i).Value
Next
Debug.Print "Test 1: " & Finish

Start
For i = 1 To 10000
lngTemp = .Cells(i, 1).Value
Next
Debug.Print "Test 2: " & Finish

Start
Set rng = .Range("A1")
For i = 0 To 10000 - 1
lngTemp = rng.Offset(i, 0).Value
Next
Debug.Print "Test 3: " & Finish

End With

End Sub
 
Top