The idea is to automatically activate a noisy siren from a sound file when opening the excel file as the market index value drops to, or is below, the minimum expected value. The market index value is refreshed every 30 minutes.
Eg. the index value alert is 4,300. If the market index value is below 4,300, the noisy siren will be automatically activated.
I first set a WebQuery for the market index latest price with automatic refresh every 30 minutes. Then, apply VBA macro to call a sound file.
I modified the VBA codes taken from ExcelTip.com and J-Walk.com as follows:
Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
-------------------
Sub PlayWavFile(WavFileName As String, Wait As Boolean)
If Dir(WavFileName) = "" Then Exit Sub
If Wait Then
sndPlaySound WavFileName, 0
Else
sndPlaySound WavFileName, 1
End If
End Sub
-----------------------------
Sub PlaySoundAlert()
For i = 1 To 5
PlayWavFile "c:\WINDOWS\Media\notify.wav", True
Next i
End Sub
-----------------------------
Function Alarm(V)
x = Range("alert")
If V <= x Then
Call PlaySoundAlert
Alarm = True
Exit Function
Else
Alarm = False
End If
End Function
