Play sound when condition is met, but not continuously while condition remains satisfied?

I pull in real-time stock quotes via DDE, which update every second. I've built a model that looks like the below showing the current Bid and Ask prices, and the difference between the two (aka the "Spread") in col. L. I want to track whenever the Spread for any given row of data is 0.15 or less -- using conditional formatting, I've managed to highlight/outline the cells that meet that condition in yellow font, BUT since Excel is often in the background, I want to create a sound alert for when a spread narrows to 0.15 or less.

The tricky part is that I'd like the sound to play every time the spread drops from above 0.15 to 0.15 or less, but I don't want it to play continuously when the spread is just staying below 0.15...my noob efforts to implement code I've found online have resulted in hearing the beep-alert every second, because the DDE feed is constantly refreshing the data, so I can't just tell the alert to play when cell value =< 0.15. When a spread drops from 0.20 to 0.15 I want an alert...if it stays there or drops further I don't want an alert. But if it later rises to 0.20 and then drops to 0.15 again, I want the alert. aka: I want the alert to play every time the condition goes from an unsatisfied condition to satisfied, but not while it remains satisfied.

spread.jpg

Yeah, I was trying to come up with something along those lines -- another column that tracks the most recent value -- but remember that the DDE feed literally updates every single second automatically (it's real-time stock quotes)...so there's no time to do anything manual here.

But I started thinking that there must be some way to set up a rule / macro that doesn't need to compare one cell to ANOTHER, but could compare the value in a cell to a previous value in the cell, no? Like...forget the example in my OP, but if the objective were simply to sound a beep every time a cell value changed to a different value (via an automatic streaming feed, forgetting about my requirement of measuring a threshold and only sounding it when the threshold were crossed)...that seems like a straight-fwd enough task, right? But I can't come up with a way for an Excel cell to "remember" what it's previous value was...

Is there a way to record the 'last' value that appears in a cell that's getting updated dynamically in an automated fashion? Perhaps with some sort of 'delay' variable? Like, for instance, if I knew that the DDE feed was updating the entire sheet every, say, 2 seconds (I'm not sure what it is but i can find out), would some sort of 'delay' that pulled the value of the cell every 2s, but OFFset by 1 second vs. the target cell work? I'm getting a little bit creative / out of my element here, so no idea whether excel/vba can work on a 'timer' like this.

Please forgive me if I'm missing something... but can't you just set a switch? (Change the value of a variable from 0 to 1. For example, switch_value = 1.)

If the value drops and you want the sound played, check to see if the switch _value has already been set. The first time thru, it will not. It's at 0. So you play the alert sound and set a switch_value to 1.

You never play the sound if the switch_value is set to 1. A 1 indicates the alert sound has already been played.

Now, if the value later rises above your threshhold, change the switch_value back to 0. This way, if it ever drops again, the sound will again be played.

In COBOL we often used what we referred to as first_time_thru switches. The first time thru a module, we wanted different statements executed than we did later, on subsequent loops through this same subroutine. So we set what we called a first_time_thru switch. Initially it's set at 0. The first time thru we set it to 1, and it remained 1 for the duration of the program. Your switch is similar, but it will get changed from 0 to 1 back to 0, accordingly.

Pseudocode for what I was talking about above:

Please Login or Register  to view this content.