Alarm 2

[cmiedaner]

Excel 2010

I have an earlier post related to this one. I am trying to set-up an audible alarm when a cell has a value greater than 0.

I was using this VBA for my alarm function:

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

It worked at one time but now I get #NAME? in the cell that I use the function.

Can you help me troubleshoot this error ?

 

[Ben McClave]

I think it may have to do with one of two things:

1. On the line starting "Call PlaySound...", the Const is missing an "A" ("SND_SYNC" vs "SND_ASYNC").

2. If that doesn't solve it, you may check that the Alarm function is located in a Module other than one of the Sheet modules or the ThisWorkbook module. When I pasted the code to the ThisWorkbook module, I also received a #NAME? error, but pasting it to Module1 worked fine.

 

[cmiedaner]

I think it may have to do with one of two things:



1. On the line starting "Call PlaySound...", the Const is missing an "A" ("SND_SYNC" vs "SND_ASYNC").



2. If that doesn't solve it, you may check that the Alarm function is located in a Module other than one of the Sheet modules or the ThisWorkbook module. When I pasted the code to the ThisWorkbook module, I also received a#NAME? error, but pasting it to Module1 worked fine.

Thanks. I tried both suggestions and still get #Name?

I found another code that I am trying to get to work:


Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" (ByVallpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long


Public Function PlayWavFileIF(WavFile As String, Condition As Boolean) As Boolean
Const SND_ASYNC = &H1, SND_FILENAME = &H20000
If Condition Then PlaySound WavFile, 0, SND_ASYNC Or SND_FILENAME
PlayWavFileIF = Condition
End Function

In A1 I have this formula =PlayWavFileIF("C:\Users\Administrator\Desktop\sound1.wav", B1=1).

I can get the formaula to change from False to True.

However, when it is True, I can get the sound1.wav to play.

Any thoughts on this ?