Macro to play a WAV only when a threshold is crossed (not when an absolute condition is met)

d0rian

Active Member
Joined
May 30, 2015
Messages
263
I get streaming stock quotes via DDE that update every 5 seconds. I just discovered how to use VBA and get Excel to play a WAV sound file when the value of a cell meets a condition, BUT I only want the sound to play when the cell value goes from being in a state of NOT meeting the condition to meeting it.

E.g. A stock is trading at $5.10, and I want the sound to play whenever the stock prices drops BENEATH $5.00. My first attempt resulted in the sound playing continuously when the price is < $5.00 (because the quotes auto-update every 5s, which "updates" the value of a cell and plays the sound every time, which is really annoying). So I want to restrict the sound to ONLY play when the price goes from $5 or higher to $4.99 or below. To be clear, I don't mean a one-time-only sound. I want it play EVERY time the value goes from being $5 or above to being below $5. e.g. if every 5 seconds, the streaming quotes that populate cell A1 are:
- $5.10 (no sound)
- $5.00 (no sound)
- $4.99 (sound)
- $4.95 (no sound)
- $4.99 (no sound)
- $5.03 (no sound)
- $4.95 (sound)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

d0rian

Active Member
Joined
May 30, 2015
Messages
263
OK. I'm probably not doing things anywhere close to optimally, since I just learned this yesterday. But I created a Module with the following code (which, AFAIK, is basically defining a new function called "Alarm")

Code:
'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 [B]Alarm[/B](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:\Users\username\Documents\Sounds\WAV\Click-1.wav" 'Edit this statement
        Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
        Alarm = True
        Exit Function
    End If
ErrHandler:
    Alarm = False
End Function
Then, in my spreadsheet, quote prices are continuously streaming into cell A1, and in the cell beneath it, I typed: =alarm(A1,">=10"). So this has the effect of playing Click-1.wav every time A1 is equal to or greater than 10. But like I wrote in my OP, it plays the sound every 5 seconds (when the quote is >10) because that's how frequently the data is auto-updating via DDE. I want to tell it "Hey, only play the sound when the cell auto-updates from a value that's less than 10 to one that's more than 10...not simply every time it updates and the value is >10."
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,188
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top