Creating a Conditional StopWatch/Timer

[Aloa123]

Hello All,

I have a VBA/Excel issue that I can't seem to figure out. I have a spreadsheet that reacts to live data feeds, and occasionally certain live feeds come in incorrectly. As of right now, i have conditional statements to alert me if something is triggered. The twist is that it is really only substantial if the alert/condition exists for more then ~5minutes. So, here's the issue -- How do I create a conditional stopwatch/timer that will begin a countdown once the initial conditional statement is true and restart if the initial conditional statement switches to false.

To simplify, If A1 is true => start a stop watch. If A1 stops being true => Stop the stop watch and reset it back to zero. And of course a secondary alert conditional statement if Stopwatch>5minutes (but that won't need to be macro code).

Thank you!!

[Leith Ross]

Hello Aloa123,

Welcome to the Forum!

It would help to know what type of live feed you have. Once that is known, it may be possible to provide you with a solution. If you can post a copy of the workbook that would be best.

[Aloa123]

Thanks for responding! Since I'm new to VBA and am not taking any formal classes these forums help a TON!

The live feeds are a reuters feeds of specific stocks bid/ask spreads. The idea is to trigger an alarm once certain conditions have been met and 5minutes has elapsed. I generated the following code, but its flawed because if it's false it'll automatically end, and if its true it'll only run once and will actually stop all the other feeds as it runs. Although it could be that I'm using an incorrect control (have it in a button).


Sub generate()

Dim i As Integer
Dim trigger As Boolean
trigger = Range("L13").Value

If trigger = True And i < 31 Then

Do While trigger = True And i < 30
Application.Wait (Now + TimeValue("0:00:01"))
i = i + 1
Loop

If i = 30 Then
MsgBox ("An unacceptable spread has existed for 5 minutes")
'Call SpreadEmail
End If
i = 0

End If



End Sub



Let me know if you guys have any ideas! Thanks for the help!!

[Aloa123]

So, I made a couple changes to the code that fix the issue of it running only once. Now the issue is just getting a pause style function in there that will allow for 5minutes to pass before the msgbox/alert while allowing feeds to still be active... It's a rough one but i really appreciate any help!

Sub generate()

Dim i As Integer
Dim trigger As Boolean
trigger = Range("L13").Value
Dim dtime As Date

dtime = Now + TimeSerial(0, 0, 10)
Application.OnTime dtime, "generate"

If trigger = True And i < 31 Then

Do While trigger = True And i < 30
'Application.Wait (Now + TimeValue("0:00:01"))
'Application.OnTime Now, TimeValue("00:00:01")
i = i + 1
Loop

If i = 30 Then
MsgBox ("An unacceptable spread has existed for 5 minutes")
'Call SpreadEmail
End If
i = 0

End If



End Sub