"Clocking" events in Excel

P

Pete

I’m writing a psychological test which will flash simple 4-choice multiple
choice questions and score how quickly you hit the 1, 2, 3 or 4 key. I need
to CONSISTENTLY measure very small time differences in how long it takes a
person to answer each question after it appears on the screen. My goal is to
use Visual Basic and Excel to “clock†such events in a very standard way,
across different instances of taking this test on the same PC each time.

Four questions:

1) Can the Auto Save or Auto Recovery functions be shut off by a Visual
Basic command?

2) On the same PC from one test-taking event to the next, will the clock
slow down if there is a reduction in available memory or increase in the
number of background apps that are running?

3) If the answer to (2) is YES, is there still a way to have the program
automatically recalibrate itself? For example by picking some information
about CPU usage or speed off of the Performance Tab of the Windows Task
Manager?

4) If clock degradation exists, is it predictable or irregular? If the
clock degradation is predictable, couldn’t the calibration to an internet
clock still be done by taking two time snapshots, say a day apart, the day
before the next test? By taking a long time interval for the two
measurements, any issues with internet connection speed would be negligible.

Thanks!

Pete
 
M

Myrna Larson

What do you mean by "very small"? The built-in VBA Timer function returns the
number of seconds that have elapsed since midnight. The following shows a
resolution of 0.015625 seconds (t - t0).

Sub TestTimer()
Dim t As Single
Dim t0 As Single

t = Timer
Do
t0 = Timer
Loop While t0 = t

Do
t = Timer
Loop While t = t0

Debug.Print t0, t, t - t0
End Sub

Charles Williams developed the following code for a high resolution timer
(millisecond resolution, AIR):

'CLASS MODULE CHighResTimer
'copy all of this code to a class module in
'the workbook that needs the timer

Option Explicit

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

'Get counter's current value?
Private Declare Function GetCount Lib "kernel32" _
Alias "QueryPerformanceCounter" ( _
lpPerformanceCount As Currency) As Long

Private TimerFreq As Currency
Private TimerOverHead As Currency
Private TimerStarted As Currency
Private TimerStopped As Currency

Private Sub Class_Initialize()
Dim Count1 As Currency, Count2 As Currency

'Get the counter frequency
GetFreq TimerFreq

'Call the hi-res counter twice, to check how long it takes
GetCount Count1
GetCount Count2
TimerOverHead = Count2 - Count1
End Sub

Public Sub StartTimer()
GetCount TimerStarted
End Sub

Public Sub StopTimer()
GetCount TimerStopped
End Sub

Public Property Get Elapsed() As Double
Dim Stopped As Currency

If TimerStopped = 0 Then
GetCount Stopped
Else
Stopped = TimerStopped
End If

If TimerFreq > 0 Then
Elapsed = (Stopped - TimerStarted - TimerOverHead) / TimerFreq
End If
End Property
1) Can the Auto Save or Auto Recovery functions be shut off by a Visual
Basic command?
Click to expand...

The AutoRecover object has an Enabled property. It also has a time interval
that can be set as high as 120 minutes. I don't know about AutoSave.
2) On the same PC from one test-taking event to the next, will the clock
slow down if there is a reduction in available memory or increase in the
number of background apps that are running?
Click to expand...

Yes. Windows controls the amount of time that is allocated to each running
application. AFAIK, you have no control over the amount of time that's
allocated to your app, and when.
3) If the answer to (2) is YES, is there still a way to have the program
automatically recalibrate itself? For example by picking some information
about CPU usage or speed off of the Performance Tab of the Windows Task
Manager?
Click to expand...

Does Charles' code take care for this for you? If you want to time some other
event as a control, what event would you use?
4) If clock degradation exists, is it predictable or irregular? If the
clock degradation is predictable, couldn’t the calibration to an internet
clock still be done by taking two time snapshots, say a day apart, the day
before the next test? By taking a long time interval for the two
measurements, any issues with internet connection speed would be negligible.
Click to expand...

I'm sure the speed will vary with the number of other applications that are
running. Do you have control over that? For reliable results, you might need
to shut down almost all background tasks.

A final comment: because of Windows' multitasking, a Windows app may not be
suitable for your purpose. You may need an old DOS program, but, AIR, then
your resolution is limited to 1 clock tick, ~ 0.05 seconds. Assuming you're at
a university or college, what software do your colleagues use for this sort of
thing?
 
P

Pete

Thanks Myrna! I am not associated with a university--I am just trying to
test human reflexes. Instead of wasting time with a video game I think it
would be intersting to have a program that you could run for a minute and
test yourself a few times a day--e.g. how are you doing when you are
well-rested versus over-tired etc.

APP wise I am just running a PC with Windows XP and Office 2003.

I am not sure but I think the fifteen one-thousandths of a second you cite
should be fine! Or even the .05 if I go with a Dos app. I'll look over what
you have sent and test it out. In all likelihood it seems this could work if
the other apps are shut down when running the test. Thanks very much!

Regards,
Peter
 
Top