Alarm Clock in Excel

geo77

New Member
Joined
May 26, 2009
Messages
30
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.
TimeInJapan.jpg


Thanks, George
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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

New Member
Joined
May 26, 2009
Messages
30
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/

RLC.jpg
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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
Top