asked on

Excel Modification Request Pt3

Hello Experts,

Just one last modification to this spreadsheet. Everything is working fine, thanks to clarsen and dave. However, I really would like an alert when the price pauses for 10 seconds.

Just to explain, at certain time intervals I'm recording how long the price pauses. However, the most significant pause are those that last over 10 seconds. So, I don't want to constantly waiting for price to pause for 10 seconds before making buying or selling decisions. So, if someone could modify the code to provide a sound alert after 10 seconds that would be great.

You can use the existing code in the spreadsheet to build from in the 'soundAlertModule':

Public Sub tenminalert(onAlert As Boolean)
Dim i As Integer

If onAlert = True Then

PlayTheSound "Windows Pop-up Blocked", SND_SYNC

End If
End Sub

The code that needs modification is as follows:

Sub Make_Record(XLOG As String)
BinPath = ThisWorkbook.Path
BinFile = "ABC.TXT"
BinFileFullpath = BinPath & BinFile

Open BinFileFullpath For Append As #1 'Change path & name to suit
Write #1, XLOG
Close 1
ThisWorkbook.Sheets("mini sized DOW").Range("$AY$1").End(xlUp).Offset(1, 0).Value = XLOG
End Sub
Sub StartTimer()
'myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim X() As String
Dim XTIME As String
Dim XLOG As String
DoEvents

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
XTIME = Format(Now(), "hh:MM:ss")
'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & myCount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Make_Record XLOG
End If
myCount = myCount + 1
Else
myCount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub

Cheers

Carlton
dynamicv2.xlsm

dlmille

I would think that waiting at specific intervals of M4 or M9 (that would be HH:M4:SS or HH:M9:SS) would be too long to seek a pause at 10 seconds, so we need to add the code in the bottom half.

Change the code as follows, and advise if it works for you:

Sub StartTimer()
'myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim X() As String
Dim XTIME As String
Dim XLOG As String

DoEvents

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
XTIME = Format(Now(), "hh:MM:ss")
'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & mycount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Make_Record XLOG
End If

If mycount = 10 Then
PlayTheSound "Windows Pop-up Blocked", SND_SYNC
End If
mycount = mycount + 1
Else
mycount = 1
ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
End If
myTime = Now + TimeValue("00:00:01")
Application.OnTime myTime, "StartTimer"
End Sub

cpatte7372

ASKER

Hi Dave,

Thanks for responding.

I'm getting a Compile Error: ByRef argument type mismatch:

SND_SYNC

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

Alternatively - I got a chance to review the code, you can change all the Const declarations in the

soundAlertModule

to:

Public Const whatever....

and then that error would not occur.

Sorry it took a while to diagnose why you got the first error - I quickly gave you a patch to just call the tenminalert, but had originally coded it to call PlayTheSound directly, so you wouldn't be impacted if you changed the tenminalert() routine.

Either change the Const declarations to Public Const, or call tenminalert(true) directly - either way should work properly (the latter perhaps a bit more easily done).

Let me know how it goes!

Dave

cpatte7372

ASKER

David, once again you've come up trumps.

I decided to go with call tenminalert(TRUE)

Thanks dude.

Now, if you could trade for me on the NYSE that would be great, ha ha.....

Seriously dude you've been great.

Cheers

cpatte7372

ASKER

This guys just keeps on giving.....

cpatte7372

ASKER

Hi Dave,

I hope you're still around. I'm testing the alert now and its constantly alerting every 10 seconds.

Can you help?

Cheers

cpatte7372

ASKER

Instead of alerting just within the specified intervals its alerting every 10 seconds regardless.

Cheers

dlmille

Sorry, dude. Will suss this out in a short while.

Dave

dlmille

Ok - Your request was to sound the alarm when the pause was equal to 10 seconds. So, any time the value of AW3 is the same for the count of 10, the alarm is sounded. Note, in the code below, it is checking to see if AW3 = myValue (the last time myValue was set) and if so, then if the counter (pause counter) is 10, the alarm is sounded. Your prompt that shows TIME: PAUSE: # seconds is adjusted only on the HH:M4/9:SS, so I didn't put the alarm inside that if statement, because 5 minutes would have elapsed before I could check on the 10 second pause.

I guess I misunderstood. Can you elaborate again when you want the alarm? Do you want it to alarm when the pause is > 10 seconds, but only at the 5 minute interval?

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
XTIME = Format(Now(), "hh:MM:ss")
'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & mycount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Make_Record XLOG
End If

If mycount = 10 Then
PlayTheSound "Windows Pop-up Blocked", SND_SYNC
End If
mycount = mycount + 1
Else

cpatte7372

ASKER

Hi Dave,

I guess I didn't make myself fully clear.

At the moment, the intervals are set for 04, 09, 14, 19, 24, 29, 34, 39, 44, 49,54 59. So I would need the alarm at pauses greater than 10 seconds at the aforementioned intervals.

Cheers

cpatte7372

ASKER

Your code above needed and End Sub, and an End IF. I managed to put them in but the code doesn't work.

dlmille

I wasn't posting code for you to use, only to discuss.

>>So I would need the alarm at pauses greater than 10 seconds at the aforementioned intervals
Gotcha

Here's the code to use:

Sub StartTimer()
'myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
Dim X() As String
Dim XTIME As String
Dim XLOG As String

    DoEvents

    If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
        XTIME = Format(Now(), "hh:MM:ss")
        'If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
        If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
            XLOG = Format(Now(), "hh:MM:ss") & ": Pause " & mycount & " seconds, value' =" & ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
            Make_Record XLOG
        If mycount > 10 Then
            PlayTheSound "Windows Pop-up Blocked", SND_SYNC
        End If   
        End If
                
        mycount = mycount + 1
    Else
        mycount = 1
        ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = xlNone
        myValue = ThisWorkbook.Sheets("mini sized DOW").Range("aw3")
    End If
    myTime = Now + TimeValue("00:00:01")
    Application.OnTime myTime, "StartTimer"
End Sub

Open in new window

cpatte7372

ASKER

Going to test it in 20mins.

Cheers mate.

cpatte7372

ASKER

I couldn't wait to test the new code so I used it with my simulated trading account. If I had used it live I would have just made $97. But more importantly, it help prevent me from losing $183

It works better than I imagined.

I wish I could award you extra points. Instead all I can say is thanks man.

Cheers mate...

dlmille

Just send to my Paypal account when you hit it. Deal? :)

Dave

cpatte7372

ASKER

Thats a deal. :-)