Timer Application

Enriched

New Member
Joined
Jun 25, 2003
Messages
34
I'm writing a "Bleep Test" application for Athletics fitness testing. It all works fine the Timer function runs for a specified number time using a Do While loop and then the mighty fine "Beep" function lets out its beep and the next wait goes on and the beep etc etc. The test runs for about 25 minutes through 23 Levels.

Code Snippet

For X = 1 To 7
PauseTime = 8.471 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime

Loop
Finish = Timer ' Set end time.
TotalTime = Finish - Start ' Calculate total time.
Range("A" & X).Value = TotalTime
Range("B" & X).Value = "Level 1"
Range("C" & X).Value = "Split " & X

If X < 7 Then Beep Else MultiBeep

Range("H18").Value = 1
Range("H20").Value = X

Next X
Counter = 7

I wanted to be able to stop the Bleep Test with another button on the page BUT the first Macro is already running so it ignores the button press to stop execution. Any ideas?

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
What about redesigned the whole methodolgy so that you have no macros running during the "pause", but have an event waiting to restart a macro, by using the OnTime method. Your "Stop" button would then cancel any waiting events.
 
Upvote 0
And the time between beeps is what exactly, please?
 
Upvote 0
Well, you want to pause for a time specified in thousandths of a second but the Timer function only returns time in tenths of a second.

You might want to look at the Windows APIs SetTimer and KillTimer.

You may also want to look at
VBA Timer object for developers (beta version)
http://www.tushar-mehta.com/excel/software/vba_timer/

Enriched said:
Good idea but it appears the TimeValue function in VBA does not support fractions of a second. :-(
 
Upvote 0
The time between the Beeps is the time it should take an athlete to run 20m. the first level is at 8.5km/h and then every minute this decreases by 0.5km/hr which means they have less and less time to get over the 20m so 10ths of a second count believe me. Interestingly (or not) David Beckham could do all twenty three levels of Bleep Test and was a National Cross Country Champion - Not a lot of people know that.
 
Upvote 0
So the timing part is OK for you now, you just need to be able to stop it when you want. Does hitting Escape bring up the "Continue? End? Debug?" prompt? If you could make that stop without the prompting, would that do?
 
Upvote 0
The Escape Key doesn't work but you can CTRL-BREAK, however I have a really inelegant solition of:- if any data is found in a particular cell then then the VBA halts! Thanks for the advice.
 
Upvote 0

Forum statistics

Threads
1,226,498
Messages
6,191,377
Members
453,655
Latest member
lasvegasbuffet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top