How does Excel Alarm Function work??

[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

 

[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!

 

[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

 

[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

 

[joeshow]

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