Problem with playing sound code

E

EMoe

Hello,

I have this code from j-walk.com website (The Alarm Function).

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 this
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

I found a wav clip on my computer c:\windows\media\ringin.wav

I don't know how to place this path in the code, nor reference a cell
to evaluate.

Need some help on this one,
Thanks, EMoe
 
G

Greg Wilson

Paste the below code to a standard module. As an example, in any cell except
A1 paste the following formula:
=Alarm(A1, ">=100")
If sound is enabled on your computer then when the value in A1 >=100 then
you should hear a "ding" sound. Works for me. Of course, you can make it
refer to any cell (except the cell containing the formula) and any condition
you like.

Regards,
Greg

Option Explicit
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 = "c:\windows\media\ringin.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
 
G

Greg Wilson

Further to my post, instead of just having the cell containing the formula
return either True of False, you can make it return a suitable text message.
Be advised that this is the first time I've used this myself. But I hav't
found a problem so far. Example with text message:

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 = "c:\windows\media\ringin.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = "Limit of 100 reached or exceeded !!!"
Else
Alarm = "Current value < 100"
End If
Exit Function
ErrHandler:
Alarm = "Error: " & Err.Description
'Alarm = False
End Function
 
E

EMoe

This is great.

"New Test"!!!

Is there a way to loop this code so that the sound will play over and
over until the cell condition becomes false again?

Thanks,
EMoe
 

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
playing wma files 2
Alarm function, disable sound if data is erased 1
Wavfile playing trubcated 3
sound 4
Play and stop sound 0
Playing wavfiles from excel 3
Run-time error question 0

Top