Using Excel or VBA to calc times in hundreths of seconds

[Nutty]

Hi folks,

Could anyone please suggest how
one could use Excel to calculate
between two time periods to
one-hundreths of a second (ie
to use Excel like a stop watch).

I can use the Now() function, but
that only gives accuracy to the nearest
second.

Any help would be appreciated.

Thanks

[Bernie Deitrick]

Nutty,

You need to use a high resolution timer. Do a Deja search for
CHiResTimer and you'll find a posting that explains how to do that.

HTH,
Bernie

[Nutty]

Bernie,

Thanks for your quick reply.

I had a look @ deja.com and no articles
were found. I did find a a result through google
(link below), however it appears to be a
manual about JCALS. There seems to
be a lot of information in there which is
very deep.

Is there a chance HrTimer would already
be availble (precompiled) for use within
Excel / VBA?

( http://jcals.monmouth.army.mil/segmented_products/csc/airm/pages/frame.htm

Thanks again,

Peter

"Bernie Deitrick" <dei...@consumer.org> wrote in message
news:3A7809A1...@consumer.org...
Nutty,

You need to use a high resolution timer. Do a Deja search for
CHiResTimer and you'll find a posting that explains how to do that.

HTH,
Bernie

<snip>

[Bernie Deitrick]

-----Original Message-----
Nutty,

Here it is....

<SNIP>
You will probably get better results using the high-resolution timers:

Create a class module called CHiResTimer and paste in the following (except for
the subroutine at the end):

Option Explicit

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" ( _
lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As Long

'Variables to store the counter information
Dim cFrequency As Currency
Dim cOverhead As Currency
Dim cStarted As Currency
Dim cStopped As Currency

Private Sub Class_Initialize()
Dim cCount1 As Currency, cCount2 As Currency

'Get the counter frequency
QueryFrequency cFrequency

'Call the hi-res counter twice, to check how long it takes
QueryCounter cCount1
QueryCounter cCount2

'Store the call overhead
cOverhead = cCount2 - cCount1

End Sub

Public Sub StartTimer()
'Get the time that we started
QueryCounter cStarted
End Sub

Public Sub StopTimer()
'Get the time that we stopped
QueryCounter cStopped
End Sub

Public Property Get Elapsed() As Double

Dim cTimer As Currency

'Have we stopped or not?
If cStopped = 0 Then
QueryCounter cTimer
Else
cTimer = cStopped
End If

'If we have a frequency, return the duration, in seconds
If cFrequency > 0 Then
Elapsed = (cTimer - cStarted - cOverhead) / cFrequency
End If

End Property

The timer can then be used like:

Sub Test()

Dim oTimer As New CHiResTimer
Dim i As Long

oTimer.StartTimer

For i = 1 To 1000000
Next

oTimer.StopTimer

Msgbox "1 million iterations took " & oTimer.Elapsed & " seconds."

End Sub

*****************************************************************

[Mike Lindsay]

It may not be as accurate as you want but Excel does have time
to the hundredth of a second! Although it doesn't actually behave
like I would expect.

Try placing the formula "=now()" in a cell such as A2
Then use custom number format for that cell of say something like:
"mm:ss.00"

I added a forms button and then the following macro:

Sub Button1_Click()
Range("a3").Value = format(now(),"nn:ss.00")
End Sub

Funny part is A3 rounds display to seconds but A2 is updating in hundredths
with each button click! This is the recalculate method doing the actual
update
of A3 as confirmed shift-F9 and F9 updating the time stamps. It seems that
the
VBA format command does not understand the hundredths of a second concept
and this limits its usefulness! You can copy the time stamp data and paste
it into
another cell to preserve its last value. You might want to play with this
for a while.

"Bernie Deitrick" <dei...@consumer.org> wrote in message
news:3A7809A1...@consumer.org...

[Mike Lindsay]

Let me answer myself on this one a little bit.
I see that the worksheet function now() returns
a 15 digit precision number so it does include
time to hundredths of a second. Problem is that
Microsoft limited both real and variant data types
to only 12 digit precision so you lose the required
information in most VBA calculations. It does look
like at least some of the worksheet functions may
retain this 15 digit precision. An example is the following
formula: "=mod(now(),1)" which, it appears, does
still retain the 15 digit precision.

Not a lot of help on this one was I!

for "Mike Lindsay" <no1b...@dtsnia.net> wrote in message
news:95aptl$44f$1...@news.chatlink.com...

[Peter Owens]

How about using the TIMER function?

Try this. (setup a command button and put the following code in it:

ie..
Sub CommandButton1_Click()

If Range("A1").Value = "" Then
Range("A1").Value = Timer
Else
MsgBox Timer - Range("A1").Value
Range("A1").Value = ""
End If

End Sub

Regards,

Peter

In article <95bevl$t4q$1...@news.chatlink.com>,

Sent via Deja.com
http://www.deja.com/

[Mike Lindsay]

Lets try this one more time!
My brain is often a little slow since I retired in 1999!

Try entering the formula "=now()" into cell A1
Custom format the cell "hh:mm:ss.00"

NOTE: Must be time format not date or it will not work!
Date formats tend to round off to seconds!!!!

Now use the same custom format on cells C2:C4
C2 is Start Time and C3 will be Stop time.
in cell C4 enter formula "=C3-C2" This will be the
elapsed total time and it will probably display ##############
between start and stop actions so don't worry.

Open a VBA module and insert the following code:

Sub starttime()
Range("a1").Calculate
Range("c2") = Range("a1").Value
End Sub

Sub stoptime()
Range("a1").Calculate
Range("c3") = Range("a1").Value
End Sub

Sub howfast()
starttime
stoptime
End Sub

I set up buttons to run these macros but the can be called
by anything you want. Howfast() was just to see what the minimum
time spread could be. I'm using a 300MHZ AMD-K6, Windows-NT 4.0,
and Excel-97 and typical got a run time of 00:00:00.01 or less!

It may not be the most accurate but it just might do what you need.

"Nutty" <goi...@optushome.com.au> wrote in message
news:0sTd6.29065$Xx3.1...@news1.eburwd1.vic.optushome.com.au...

[Mike Lindsay]

Thanks once again for the heads up on the Timer
function! Just goes to show you can teach an old
dog new tricks sometimes!

[Myrna Larson]

>Problem is that Microsoft limited both real and variant data types
>to only 12 digit precision so you lose the required information in most VBA
>calculations.

I think you've gotten something confused here.

1. There is no "real" data type. In VBA, there are three floating-point
numeric types:

Single: 4 bytes, 32 bits, 6 digits of precision
Double: 8 bytes, 64 bits, 15 digits of precision
Decimal: 12 bytes, 96 bits, 28 digits of precision

Singles and doubles are stored in standard IEEE format. For doubles, that is 1
bit for the sign, 11 bits for the exponent, and 52 bits for the mantissa.
Decimal is a sub-type of Variant.

2. A variant includes a byte that indicates the underlying data type of the
current contents, plus bytes that contain data itself (except for strings).

If a variant contains a number, it contains either a Byte, Integer, Long
Integer, Currency, Single, Double, or Decimal.

Let's say you have this code:

Dim v As Variant
Dim d As Double

d = 1287.418#
v = 1287.418#

The data bytes in the variable v would be identical to those in d.

3. Excel seems to always store numeric worksheet cells values as Doubles, even
it they are Integers.

[Nutty]

> It may not be the most accurate but it just might do what you need.

Mike (and everyone else), thank you all for your help.
The timing now works to 1/100th of a second, which is
more than accurate given that someone is pressing the
stop / start button.

Once again,

Thank you all!

----- Original Message -----
From: "Mike Lindsay" <no1b...@dtsnia.net>
Newsgroups: microsoft.public.excel.programming
Sent: Thursday, February 01, 2001 11:14 PM
Subject: Re: Using Excel or VBA to calc times in hundreths of seconds