Excel Modification Request

Hello Experts,

I haven't asked for Excel help in a while - I hope you Excel gurus can assist me.

Anyway, in the attached spreadsheet an expert helped tweak the existing formula to send provide a sound alert whenever the time paused for 15 seconds or 10 seconds at the following times "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59". So for example, the the price paused for 15 seconds at 16:54 or 17:44 or 15:39 or 19:19 etc.... I would get a sound alert (The same applies for pauses at 10 seconds)

Now, that fines. The problem is the spreadsheet is also showing all the pauses outside the specified times of "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59". So if you look at the example you will see prices at 16:56, 16:55, 16:57. Can someone show me how I can keep the alerts at the specified times without showing all the other times?

I hope you guys can understand what I'm getting out.

Please let me know if you need further clarification.

Cheers

Carlton
Price-Pause-v5.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.

CSLARSENCommented:
Hi
The whole sheet returns #NAME? since e,g, xlqIntraOpen function is not defined in the sheet you uploaded.

Freeze some of the values so VBA code can run without problems related to the missing functions.
cheers
cslarsen
0
cpatte7372Author Commented:
CSLARSEN,

Thanks for responding. Sorry about that. I will freeze and re-upload in two minutes

Cheers
0
cpatte7372Author Commented:
Hi CSLARSEN,

As you'll see from the new upload the unwanted numbers are those that don't end with "04", "09", "14", "19", "24", "29", "34", "39", "44", "49", "54", "59", e.g. 18:56, 19:00, 18:55 and so on.

Cheers mate..
Price-Pause-v6.xlsm
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

CSLARSENCommented:
Hi
I checked the code in starttimer,
from my point of view the nested IF and Double select  X(1) statements make it difficult to control when a record should be inserted.

e,g, like this
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

after having determined ONCE whether a record should be created call the sub with the xlog parameter. In short: tidy up the nested if statements and determine ONCE when to dump the record.
Right now it is almost impossible to trace when what happens. at least for me :-)
cheers
cslarsen
0
cpatte7372Author Commented:
cslarsen,

Thanks for trying mate. Unfortunately, the formula was coded for me by an expert. I have very limited coding ability. Therefore, I wouldn't know where to begin tidying up the nested statements.....


Cheers
0
cpatte7372Author Commented:
Are there any other Experts able to help?

Cheers
0
CSLARSENCommented:
Hi again,
Could you explain the logic why do you count the number of times the Starttimer runs?
or even better What do you want this sheet to do.
I understand it as at minute intervals ending with "04" or "09" and so on, you want to track with 10 or 15 sec intervals a certain price. Am I correct?
cheers
cslarsen
0
CSLARSENCommented:
do you want to switch forth and back between 10 and 15secs intervals Why? is it critical?
In your current code Mycount NEVER gets reset to run between 10 and 15and this does not seem right

cheers
cslarsen
0
cpatte7372Author Commented:
cslarsen, thanks for sticking with me on on this. Due to the time difference I wasn't able to respond sooner.

The starttimer represents the time the price starts. The start timer begins each time a price changes. For example, the if the price is 11000 at 15:01 the timer will start at 15:01, if the price then changes to 11001 at 15:03, the timer will will start at 15:03.

This is needed because I need to get an alert if the price remains the same for either 15 seconds or 10 seconds.

The reason its critical is because I need to know how long a price remains the same when trading.

Does that make sense?

Cheers
0
CSLARSENCommented:
ok , so you want a "yellow" alert after 10 secs and then "red" after 15 secs.
So how do you want the alert when it is the same for more than 15 secs?
I understood it as it should alert "red" 4 times if it is the same in within minutes 4-9-14-19......
and nothing in other minutes. Correct?
thx carsten
0
CSLARSENCommented:
I suggest to check whether the minutes are 4 or 9 before doing anything.
E,g, something like this and then afterwards look into the 10 and 15.
cheers
carsten

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, 8, 1) = 4 Or Mid(XTIME, 8, 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
0
cpatte7372Author Commented:
CSLARSEN

Thanks again for sticking with me with this. I haven't had a chance to test your formula. Will try it shortly.

BTW, its Green alert after 15 seconds. Will that impact your formula?

Cheers
0
cpatte7372Author Commented:
cslarsen,

Do I need replace existing code with your code?

0
cpatte7372Author Commented:
cslarsen,

I've pasted in youir code, but nothing appears to happen....
0
cpatte7372Author Commented:
Oh cslarsen, even its not working, I think I can see what is meant to happen.

The code is assessing the seconds instead of the the minutes. For example, its looking for 18:51:29 etc. When it should be looking for 18:29:xx. I'm not concerned with the seconds but when a pause occurs at the minute of 04, 09, 19, 24, 29, 34, etc. Whereby your formula is looking when pauses occur at the seconds.

The way you've done is brilliant. If you could just tweak it for minutes and i can test it after.

I would do it myself, but I really don't understand coding very well.

Cheers mate.
0
CSLARSENCommented:
Hi again
Sorry for the missing change back to minutes.
Enter X(1) instead of X(2) i changed it to faster check whether something would happen at the right time.
cheers
cslarsen
0
CSLARSENCommented:
Hi again
pasted the wrong section
change in my code :
insert
If Mid(XTIME, 5, 1) = 4 Or Mid(XTIME, 5, 1) = 9 Then
and remove
If Mid(XTIME, 8, 1) = 4 Or Mid(XTIME, 8, 1) = 9 Then

 i changed it to faster check whether something would happen at the right time.
cheers
cslarsen
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
cpatte7372Author Commented:
Hi cslarsen,
Thanks for responding. I'm testing now....

Cheers
0
cpatte7372Author Commented:
Cslarsen,

This is diamond mate. Absolutely brilliant. Its actually better than I imagined.

Don't know how to thank you.

Cheers mate.
0
cpatte7372Author Commented:
The man is a genius.

Cheers
0
cpatte7372Author Commented:
cslarson, there appears to be a problem when I insert the code into my live spreadsheet.

When the price changes dynamically, the time doesn't change.

It works if I manually make the change, but as I mentioned when the price changes dynamically it doesn't work.

I wonder if you could help?

Cheers
0
CSLARSENCommented:
of course and thx for the grade,
would you mind uploading the file as you have it now?
thx carsten
0
cpatte7372Author Commented:
Hi cslarsen, thanks for responding. In your absence I managed to get the code to dynamically. However, the sound alert that I got help with constantly alerrts outside the intervals.
dynamic.xlsm
0
cpatte7372Author Commented:
The affending code is:

End If
     If myCount = 10 Then
            Call tenminalert(True)
        End If
    myCount = myCount + 1
0
CSLARSENCommented:
Hi again,
I have checked the trigger of the calltimer event. You use Worksheet_Change.
A formula changing does not trigger the worksheet change event thus no start of the Starttimer occurs.

Maybe Worksheet_calculate or Worksheet_selectionchange will do the trick for you.
cheers
cslarsen
0
cpatte7372Author Commented:
Hi,

I did as you mentioned and got an ambigous error message at:

If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
0
cpatte7372Author Commented:
Sorry I meant, 'Application-defined error message
0
CSLARSENCommented:
Hi again,
It happens because Myvalue is empty, and you can not assign an empty value to a cell.
I would attempt to assign a value to Myvalue first to avoid the problem.
cheers
cslarsen
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.