Proc to measure time intervals

[Neal Zimm]

Hi -
I needed a tool to measure how long different versions of
some macros I'm writing take to execute, AND, I wanted the
precision in at least tenths of a second; (partly as a learning
experience, too).
Other postings here and my own efforts resulted in my NOT
getting the hh:mm:ss.0 format to work...
so I wrote my own proc. It's at the end of this writeup
and it works in initial testing. (SecsSpan)

However, in the code below to test it further, an 'odd' thing
is happening. I had thought that by varying the 'To' value
in the HowMany loop by an increment, that the resulting time
it took to execute would vary "kinda" proportionately.
I don't think the SecsQty values are proportional enough.
It casts doubt on SecsSpan being a tool I can rely on.
e.g. 4MM and 12MM iterations both take damn close to 1 second.

1) What are the changes to the SecsSpan proc to make it better?

2) There are no other applications running, on my home computer.
Any ideas on why the SecsQty values are not more proportional?

3) How possible is it that the howmany loop is preventing the
Excel "timer" from doing its thing ?

thanks much,
Neal Z.

Dim JumpMillion as Integer, Sum as Long, Date1 as Date, HowMany as Long
Dim Date2 as Date, SecsQty as Double, ShowIt as String

Date1 = 38802.0000001157
Date2 = 38802.0000008099 'diff is close to .06 seconds
Call SecsSpan(Date1, Date2, SecsQty)
ShowIt = Format(SecsQty, "0.000000000000")
MsgBox ShowIt & " seconds", , "about .06"

For JumpMillion = 1 To 20 ' force varying execution times
Sum = 0
Date1 = Now
For HowMany = 1 To (JumpMillion * 1000000)
Sum = Sum + 1
Next HowMany
Date2 = Now
Call SecsSpan_Calc(Date1, Date2, SecsQty)
ShowIt = Format(SecsQty, "0.000000000000")
MsgBox ShowIt & " seconds", , "HowMany = " & HowMany - 1
Next JumpMillion
Exit Sub


Sub SecsSpan(ByRef BeginTime As Date, _
ByRef EndTime As Date, ByRef SecsQty As Double)
' Calculate a time span to enable display in _
at least tenths of seconds.
'Development of the constant:
' Mar 26, 2006 = 38802.000 etc
' Mar 27, 2006 = 38803.000
' 1 day has 86,400 seconds or 8,640,000 hundreths of seconds
' 1 / 8640000 is the constant below.
Const Dot01ofSec As Double = 0.0000001157407

SecsQty = 0.01 * ((EndTime - BeginTime) / Dot01ofSec)
End Sub
'
'SecsSpan was first tested by feeding it predetermined
'numeric begin and end values, and the SecsQty result
'was accurate enough for me.

 

[Bob Phillips]

How about

Dim JumpMillion As Integer, Sum As Long, Date1 As Date, HowMany As Long
Dim Date2 As Date, ShowIt As String
Dim nStart As Double
Dim nEnd As Double

Date1 = 38802.0000001157
Date2 = 38802.0000008099 'diff is close to .06 seconds
ShowIt = Format(Date1 - Date2, "0.000000000000")
MsgBox ShowIt & " seconds", , "about .06"

nStart = Timer
For JumpMillion = 1 To 20 ' force varying execution times
Sum = 0
Date1 = Now
For HowMany = 1 To (JumpMillion * 1000000)
Sum = Sum + 1
Next HowMany
nEnd = Timer
ShowIt = Format(nEnd - nStart, "0.000000000000")
Debug.Print ShowIt & " seconds", , "HowMany = " & HowMany - 1
Next JumpMillion

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

If you look at this article, it says the Timer has only about 1 second level
of resolution. It shows some alternative times.

http://support.microsoft.com/?kbid=172338
HOWTO: Use QueryPerformanceCounter to Time Code

Charles Williams previously posted this code:

Code posted by Charles Williams:

Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long

Sub testit()
Dim j As Long
Dim str1 As String
Dim dTime As Double
dTime = MicroTimer
For j = 1 To 20000
str1 = strColid(255)
Next j
dTime = (MicroTimer - dTime) * 1000
MsgBox dTime
End Sub

Function MicroTimer() As Double
' returns seconds
' uses Windows API calls to the high resolution timer
'
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
strWhere = "MicroTimer"
'
MicroTimer = 0
'
' get frequency
'
If cyFrequency = 0 Then getFrequency cyFrequency
'
' get ticks
'
getTickCount cyTicks1
'
' calc seconds
'
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency

End Function

 

[Neal Zimm]

Thanks Bob,
I'm looking into the Timer now.

 

[Neal Zimm]

Dear Tom,
Thanks again. I've just browsed the article and will read it carefully
tomorrow, as well as the code you provided. The one second resolution of the
Now variable explains the results I got, the 'odd' ones, in trying to use it.
Silly me.
'Til the next time,
Thanks,

 

[Neal Zimm]

Tom -
In the MicroTimer function, I don't understand the line of code:

strWhere = "MicroTimer"

It looks like setting the value into a string variable, but I don't see
it referenced anywhere in the function. I have not run it yet, but
it looks like it has to be dim'd.

Can you clarify please?
Thanks,
Neal Z.