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
cpatte7372Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
0
cpatte7372Author Commented:
Hi Dave,

Thanks for responding.

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

SND_SYNC

0
dlmilleCommented:
Ok - try this - just change that line

PlayTheSound "etc......"
to

call tenminalert(TRUE)

Which will do the same thing - perhaps the scoping of SND_SYNC is private to the module where this code is...

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

dlmilleCommented:
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
0
cpatte7372Author Commented:
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
0
cpatte7372Author Commented:
This guys just keeps on giving.....
0
cpatte7372Author Commented:
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
0
cpatte7372Author Commented:
Instead of alerting just within the specified intervals its alerting every 10 seconds regardless.

Cheers

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

Dave
0
dlmilleCommented:
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
0
cpatte7372Author Commented:
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
0
cpatte7372Author Commented:
Your code above needed and End Sub, and an End IF. I managed to put them in but the code doesn't work.
0
dlmilleCommented:
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

0
cpatte7372Author Commented:
Going to test it in 20mins.

Cheers mate.
0
cpatte7372Author Commented:
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...
0
dlmilleCommented:
Just send to my Paypal account when you hit it.  Deal?  :)

Dave
0
cpatte7372Author Commented:
Thats a deal. :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.