CODEWITHSUNDEEP
excelvba
user3778967
user3778967

Reputation: 13

Excel pause/time wait macro

I have the following function on my excel spreadsheet that makes it say "trade" whenever a certain spread between 2 future contracts occurs:

Function SayIt(c As Boolean, s As String)
If c Then Application.Speech.Speak s
SayIt = c
End Function

Then on the Sheet i have:

=SayIt(D15<=G6;REPT("trade ";1))

This works great and it will say "trade" but everytime the price moves inside the arbitrage range it will repeat "trade, trade, trade, trade..."

Now what i need is a function that will either make it stop saying "trade" while the prices allow the spread arbitrage or that it will only repeat after 20 seconds or 1 minute if it is still inside the set spread.

Thanks

Upvotes: 1

Views: 284

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Great question! ........the code needs to "remember" that the previous call was given True to avoid re-speaking. We can do this with a Global Boolean:

In a Standard module:

Public Was_c_TrueBefore As Boolean

Function SayIt(c As Boolean, s As String)
    If c And Not Was_c_TrueBefore Then
        Application.Speech.Speak s
        Was_c_TrueBefore = True
    End If
    If Not c Then
        Was_c_TrueBefore = False
    End If
    SayIt = c
End Function

Because there is only one Global, this will only work for one function call.

Upvotes: 1

Related Questions