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


"Bob Phillips" wrote:

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)

"Neal Zimm" wrote in message
...
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.
--
Neal Z