Alarm Clock in Excel

[geo77]

I am writing a tutorial about using time in Excel on my blog and I made a clock in Excel which can be started and paused from the same button using the following code :

Dim RunClk As Boolean
-----------------------
Sub RunPauseClk()
RunClk = Not (RunClk)
Do While RunClk = True
DoEvents
Range("A1") = TimeValue(Now)
Range("A2") = Now()
Loop

End Sub

It works fine but I would like to add two sounds, a ticking sound and alarm sound and I have problems with triggering the first sound in sync with the "seconds" arm.
I used the following code:

Sub RunPauseClk()
RunClk = Not (RunClk)
Do While RunClk = True
DoEvents
Call PlaySound(ThisWorkbook.Path & "\button5.wav", 0&, &H2 Or &H20000)
Range("B3") = TimeValue(Now)
Range("B9") = Now()
Loop

End Sub

It produces the sound and I tried H1, H2, H4 etc and I don't seem to be able to synchronize the sound with the seconds.

You can download the file from : http://excelunusual.com/archive/2010/12/time-in-vba-japanese-clocks/ The clocks work but I removed the sound effects.

Thanks, George

 

[Ruddles]

VBA can only do one thing at a time: move the second hand or generate the sound. I suspect you're not going to be able to get them to happen at precisely the same moment.

BICBW...

 

[geo77]

Hi Ruddles, I don't need them to happen at the same moment, 10-100 ms difference is perfectly fine. VBA can start two thing at at distinct but very close time intervals by using "DoEvents". It definitely can run sound at the same time while running a calculation loop. You can check out my roller coaster file. Even better check out this RLC circuit. It runs an infinite loop of calculations and dynamic chart displays while generating sound whenever the user changes any setting without stopping the macro. So there are 3 things happening at the same time: calculations+ copy and paste, chart updates and sounds. Again this would not be possible without the use of "DoEvents". You can download this and see how it works.

http://excelunusual.com/archive/2010/11/rlc-parallel1/