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

[d0rian]

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)

 

[bob33]

What is your code? May be easy to tweak it than starting over.

 

[d0rian]

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")

'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."