How does Excel Alarm Function work??

J

joeshow

I understand that you can use the alarm function to play a sound when
certain parameter has been met, but what if the parameters kee
changing.
eg. for stock, the volume is constantly being updated and increases.
want to set an alarm that will notice when the volume of a stoc
reaches a value of its 10 day average.
therefore I need to be able to compare to separate cell references.
the following formula is what i found that sound a wav file if a cel
reference and a condition are met:

=Alarm (B13,">=1000")

the documentation also stated this following code to be used for th
alarm to sound

'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long


Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit thi
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function


if there is anyone that can help me with this problem please email m
or message me.
thanks very much in advance for anyone that can help me.
cheer
 
F

Frank Kabel

Hi
do you have a cell which stores your 10 day average. If yes change the
function call to
=Alarm (B13,">=" & cell_with_10_day_average)

BTW this is not an Excel build-feature but a tip/function from John
Walkenbach!
 
J

joeshow

i appreciate your very quick response.
i have a couple of questions

if my cell with the 10 day average was in H7 and my current volume wa
in F7 would this be the formula to compare two cell references...?

= Alarm (F7,">" & H7)

Another question i have is what does BTW mean and how is this used i
the alarm function?

Last question is how would I compare a number of stocks instead of jus
one row? Am i going to have to add this formula in for every stoc
added in the spreadsheet or is there a quick and dirty method that wil
do it?

thanks again for the insight.
talk to you later
 
F

Frank Kabel

Hi
see below
if my cell with the 10 day average was in H7 and my current volume was
in F7 would this be the formula to compare two cell references...?

= Alarm (F7,">" & H7)

yes give this a try

Another question i have is what does BTW mean and how is this used in
the alarm function?

:) just a shotcut for 'By the way'. I was just saying that this Alarm
function is not Excel standard but a user defined function published by
John Walkenbach (see http://j-walk.com/ss/excel/tips/tip87.htm)

Last question is how would I compare a number of stocks instead of just
one row? Am i going to have to add this formula in for every stock
added in the spreadsheet or is there a quick and dirty method that will
do it?
If your stocks are all stored in column F try the following
= Alarm (F7,">" & $H$7)
copy this down

Note: This can create quite a noise :)
 
J

joeshow

Thanks Frank for your help. its much appreciated.
i got a bit further than I was
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Alarm 2 2
Alarm function, disable sound if data is erased 1
playing wma files 2
sound 4
Playing wavfiles from excel 3
Run-time error question 0
Problem with playing sound code 3
Play and stop sound 0

Top