Alarm Sound Function Not Working_Any DDE Gurus??

J

joeshow

I have been trying to set up a spdsht that will compare the previous
10 day volume with the current volume. this is compared by 30 minute
increments. for example i am comparing the volume traded from 10am-
10:30am EST with the previous 10 Days at the same time period (10-
10:30am).
What i have is an alarm that goes off when todays volume exceeds the
prev 10 day average. i want to be able to have is :

1. the alarm sounds when todays volume exceeds prev 10 Day
2. Have a "stop sound" button or "reset" button that stops
the alarm for that 30 Min period, but will continue to
update after the 30 interval is complete.


This is the code that i have so far in my module:
///////////////////////////////////////////////////////
'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 & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
alarm = True
Exit Function
End If

ErrHandler:
alarm = False
End Function
/////////////////////////////////////////////////////////
Public Sub SoundBeep()
If Range("I7") = "True" Then
ActiveCell.Range("I7") = "=IF(RC[-3]>RC[-1],alarm(RC[-
3],"">H7"")"
Else
If Range("I7") = "False" Then
alarm "I7", "=0"
End If

End If
End Sub
////////////////////////////////////////////////////////////

This is the code i have in Sheet 3

Dim intResult As Integer

Private Sub CommandButton01_Click()
intResult = PlaySound(vbNullString, vbNull, SND_NODEFAULT)

End Sub
///////////////////////////////////////////////////////////

If anyone would like better clarification on any specifics please
contact me or message me. I would really appreciate some help from
those that really enjoy the nitty gitty about XL.
Thanks very much
 
F

Frank Kabel

Hi
one question regarding your 'stop button' where should this button be:
- should you get a messagebox if the alarm was issued and you can
decide whether to stop or not (this will work on individual cell base)
- do you want a global 'Stop button'?

For the first one you may try the following: Replace the alarm function
with the following adapted version:

Function alarm(Cell, Condition)
Static cond(30, 30)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Dim ret_value
On Error GoTo ErrHandler

If Evaluate(Cell.Value & Condition) Then
If cond(Cell.row, Cell.Column) < Now Or cond(Cell.row, Cell.Column)
= "" Then
cond(Cell.row, Cell.Column) = ""
WAVFile = ThisWorkbook.path & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

ret_value = MsgBox("Alarm - Stop for 30 minutes?", vbYesNo)
If ret_value = 6 Then
cond(Cell.row, Cell.Column) = Now + TimeValue("00:30:00")
End If
alarm = True
Exit Function
Else
' do nothing
End If
End If

ErrHandler:
alarm = False
End Function

------
Note: I declared a static array with the a dimension of 30 rows/30
columns. You may have to adapt this if you have your alarm cell for
example in A100



--
Regards
Frank Kabel
Frankfurt, Germany
I have been trying to set up a spdsht that will compare the previous
10 day volume with the current volume. this is compared by 30 minute
increments. for example i am comparing the volume traded from 10am-
10:30am EST with the previous 10 Days at the same time period (10-
10:30am).
What i have is an alarm that goes off when todays volume exceeds the
prev 10 day average. i want to be able to have is :

1. the alarm sounds when todays volume exceeds prev 10 Day
2. Have a "stop sound" button or "reset" button that stops
the alarm for that 30 Min period, but will continue to
update after the 30 interval is complete.


This is the code that i have so far in my module:
///////////////////////////////////////////////////////
'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 & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
alarm = True
Exit Function
End If

ErrHandler:
alarm = False
End Function
/////////////////////////////////////////////////////////
Public Sub SoundBeep()
If Range("I7") = "True" Then
ActiveCell.Range("I7") = "=IF(RC[-3]>RC[-1],alarm(RC[-
3],"">H7"")"
Else
If Range("I7") = "False" Then
alarm "I7", "=0"
End If

End If
End Sub
////////////////////////////////////////////////////////////

This is the code i have in Sheet 3

Dim intResult As Integer

Private Sub CommandButton01_Click()
intResult = PlaySound(vbNullString, vbNull, SND_NODEFAULT)

End Sub
///////////////////////////////////////////////////////////

If anyone would like better clarification on any specifics please
contact me or message me. I would really appreciate some help from
those that really enjoy the nitty gitty about XL.
Thanks very much.
Click to expand...
 
J

joeshow

I see where your going with your code. One thing i was wondering is wha
happens if the alarm goes off at for example 10:24am? Will the alarm b
stopped for 30 mins from the time the alarm is disengaged or will i
start back up when it reaches 10:30am.??

This is what im aiming for is to have the alarm work for the half hour
if the alarm goes off, i can hit a button to stop the alarm, and whe
the time hits the top of the hour Or the half hour, the alarm wil
reset.

thanks again for your solutions.
ps. i think were getting really close to solving this one
 
J

joeshow

Frank,
you mentioned:

"Note: I declared a static array with the a dimension of 30 rows/30
columns. You may have to adapt this if you have your alarm cell for
example in A100"

Im not sure about what this means and how a static array works? can yo
explain more about how it relates to your XL sheet and what i shoul
change to adapt it to mine.
thank
 
F

Frank Kabel

Hi
see below
I see where your going with your code. One thing i was wondering is
what happens if the alarm goes off at for example 10:24am? Will the
alarm be stopped for 30 mins from the time the alarm is disengaged or
will it start back up when it reaches 10:30am.??
Click to expand...

Just test it :)
It will always wait for 30 minutes after you stop the alarm in the
messagebox. If you like you can change the messagebox to an Inputbox
and enter your individual stop alarm delay - nice training for you :)
This is what im aiming for is to have the alarm work for the half
hour; if the alarm goes off, i can hit a button to stop the alarm,
and when the time hits the top of the hour Or the half hour, the
alarm will reset.
Click to expand...

O.K. that is something different from your first specification. So you
always want to stop the alarm until the next half hour or full hour is
reached. So if you hit the stop button at 11:58 the alarm will start
again at 12:00. If this is correct you may try the following (not
tested though):
replace the line

cond(Cell.row, Cell.Column) = Now + TimeValue("00:30:00")

with
cond(Cell.row, Cell.Column) =
application.WorksheetFunction.RoundUp(now*48,0)/48
 
F

Frank Kabel

Hi
for each cell I store if the alarm should be stopped or not. For this I
use a static error. So if you hit the button I insert the ending time
for the alarm stop in the static array for this specific cell. To save
memory I used an array large enough for 30 rows and 30 columns. If you
have more cells you have to adjust the statement
Static cond(30, 30)

The first parameter for the rows, the second one for the columns.
 
J

joeshow

Frank,
Thanks for the suggestion. I will try it bring and early tomorro
morning.
If they were awarding points to the excel geniuses i would give you a
least a 500 point bonus. Its great to have some experience runnin
these message boards.
One thing I wondered is if I wanted to only select columns "E" & "F
which have my comparison data in them how would i do that with a stati
control?

What im comparing is IF cell E7 is greater than F7 then my alarm shoul
go off and when i hit the Reset control button, it should stop th
sound until the half hour or top of the hour?

Hope this isnt too difficult for you.
Thanks agai
 
F

Frank Kabel

Hi
if you only use one columns for comparison you can reduce the
dimensions of the static variable:
You may try the following (note: Only one cell per row is allowed for
the ALAR functions!)

Function alarm(Cell, Condition)
Static cond(30, 1) '30 rows from row 1 to row 30 are allowed
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Dim ret_value
On Error GoTo ErrHandler

If Evaluate(Cell.Value & Condition) Then
If cond(Cell.row, 1) < Now Or cond(Cell.row, 1) _
= "" Then
cond(Cell.row, 1) = ""
WAVFile = ThisWorkbook.path & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

ret_value = MsgBox("Alarm - Stop for 30 minutes?", vbYesNo)
If ret_value = 6 Then
cond(Cell.row, 1) = Now + TimeValue("00:30:00")
End If
alarm = True
Exit Function
Else
' do nothing
End If
End If

ErrHandler:
alarm = False
End Function
 
J

joeshow

Thanks so much
I really appreciate your help Frank.
I have tested the code and this is the code that works for my Volum
Breakouts alarm function spreadsheet.

'Windows API function declaration
Public 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)
Static cond(30, 30) '30 rows from row 7 to row 30 are allowed
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Dim ret_value
On Error GoTo ErrHandler

If Evaluate(Cell.Value & Condition) Then
If cond(Cell.Row, Cell.Column) < Now Or cond(Cell.Row, Cell.Column)
"" Then

cond(Cell.Row, Cell.Column) = ""
WAVFile = ThisWorkbook.Path & "\buy.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)

ret_value = MsgBox("Alarm has been reached", vbOK)
If ret_value = 1 Then

cond(Cell.Row, Cell.Column) = Application.WorksheetFunction.RoundUp(No
* 48, 0) / 48
'cond(Cell.Row, Cell.Column) = Now + TimeValue("00:30:00")
End If
alarm = True
Exit Function
Else
' do nothing
End If
End If

ErrHandler:
alarm = False
End Function


You were the only one out of few others that could tell me how to ge
this done. I have learned quite alot in the last little bit. thank
again.
joesho
 
J

joeshow

Frank, Could i ask another question about this spreadsheet?

I have it running now and the alarms are going off when they ar
supposed to so that great.
The problem I have is when the 30 minute period is complete the messag
box comes up about 10-15 times to say Ok, before the volume column ha
a chance to zero out again.

Would you have an idea what is looping or any idea how to stop thi
alarm from going off uncontrollably at the end of every half hour??

thanks for the help
joesho
 
J

joeshow

One other thing i was wondering was about that static control.

If i have data in rows 1-50 and in columns A-I, is a static control o
(60,10) going to cover my data sheet?
 
F

Frank Kabel

Hi
the static range is sufficient. also
(50,9) would be enough

For your other question: Problem is you wanted an alarm for each cell.
Now for all cells the condition is met -> all alarms get triggered
according to your settings.
 

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
Problem with playing sound code 3
How does Excel Alarm Function work?? 4
Alarm function, disable sound if data is erased 1
playing wma files 2
sound 4
TextBox.Visible = True ... not working 3
Playing wavfiles from excel 3

Top