Problem with playing sound code

[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

 

[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

 

[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

 

[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