Solved

Additonal Excel Timer Alert and Logging

Posted on 2011-09-21
28
439 Views
Last Modified: 2013-11-25
Hello Experts,

An expert helped me with the following timer:

Sub StartTimer()
   DoEvents

   If ThisWorkbook.Sheets("mini sized DOW").Range("ar6") = myValue Then
      If myCount >= 17 Then
         ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = 4
      End If
      myCount = myCount + 1
   Else
      myCount = 1
      ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = xlNone
      myValue = ThisWorkbook.Sheets("mini sized DOW").Range("ar6")
   End If
   myTime = Now + TimeValue("00:00:01")
   Application.OnTime myTime, "StartTimer"
End Sub

I was wondering if an expert could help tweak it to add another hightight at 10 seconds, this time yellow.

Also can I have the occurance of each logged to a text file?

I already have log file set up to log an occurrance with the following formula:

Addr = Array("AQ3", "AV3", "AP3", "AU3")    'Watch these cells for price changes
Targ = Array(30, 30, 30, 30)                'Look for prices above these threshhold values
n = UBound(Stocks)
For i = 0 To n
    With Range(Addr(i))
        If Not IsError(.Value) Then
            If Stocks(i) <> .Value Then
                Stocks(i) = .Value
                If .Value > Targ(i) Then
                    Open "C:\Users\User\Documents\ABC.txt" For Append As #1  'Change path & name to suit
                    Write #1, .Address(False, False), .Value, Date, Format(Time, "hh:mm:ss.ss")
                    Close 1
                End If
            End If
        End If
    End With
Next
End Sub

I only add the formula to assist any expert able to help with the logging of the highlights.

I have attached the spreadsheet for illustration.

Cheers


Carlton
EE-DowTradingv3.xlsm
0
Comment
Question by:cpatte7372
  • 17
  • 9
28 Comments
 

Author Comment

by:cpatte7372
ID: 36575404
Experts,

Where I mentioned log I would like help logging the time and value the highlight occurred.

Thanks in advance for you help.
0
 

Author Comment

by:cpatte7372
ID: 36576597
Experts,

The logging of the highlight isn't really the important factor.

I just need to be able to log the time. So, as you will see from formula, I get an highlight when the value in ar6 remains stagnant for 17 seconds. I would like that logged to a text file, and a log for when the time remains stagnant for 10 seconds.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 36579852
OK, I've managed to modify the code to add an additional 10 seconds, with the following code:

Sub StartTimer()
   DoEvents

   If ThisWorkbook.Sheets("mini sized DOW").Range("aw3") = myValue Then
      If myCount >= 17 Then
         ThisWorkbook.Sheets("mini sized DOW").Range("aw3").Interior.ColorIndex = 4
      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("ax3")
   End If
   If ThisWorkbook.Sheets("mini sized DOW").Range("ax3") = myValue Then
      If myCount >= 5 Then
         ThisWorkbook.Sheets("mini sized DOW").Range("ax3").Interior.ColorIndex = 6
      End If
      myCount = myCount + 1
   Else
      myCount = 1
      ThisWorkbook.Sheets("mini sized DOW").Range("ax3").Interior.ColorIndex = xlNone
      myValue = ThisWorkbook.Sheets("mini sized DOW").Range("ax3")
   End If
   myTime = Now + TimeValue("00:00:01")
   Application.OnTime myTime, "StartTimer"
End Sub

Can someone please show me how to log the event to a text file?

Cheers

Also, I've noticed that the time isn't correct. For example, I have 17 seconds, but I get the highlight alert after, say 7 seconds
0
 

Author Comment

by:cpatte7372
ID: 36580072
I take it back, I didn't work out the additonal timer of 10 seconds
0
 
LVL 19

Expert Comment

by:akoster
ID: 36586151
If you use this macro routine instead of the old one :
Sub StartTimer()
   DoEvents

   If ThisWorkbook.Sheets("mini sized DOW").Range("ar6") = myValue Then
      If myCount >= 17 Then
        '-- green highlite when over 17 seconds
         ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = 4
       ElseIf myCount >= 10 Then
        '-- yellow highlite when over 10 seconds
        ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = 6
      End If
      myCount = myCount + 1
   Else
      myCount = 1
      ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = xlNone
      myValue = ThisWorkbook.Sheets("mini sized DOW").Range("ar6")
   End If
   myTime = Now + TimeValue("00:00:01")
   Application.OnTime myTime, "StartTimer"
End Sub

Open in new window

0
 

Author Comment

by:cpatte7372
ID: 36586392
Hi akoster, thanks for responding,

Going to check it out now...
0
 

Author Comment

by:cpatte7372
ID: 36586398
akoster,

Is there anyway to get the occurance of either 17seconds or 10 seconds logged to a text file?

Cheers
0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 36586444
sure, you can use this :
Sub StartTimer()
   DoEvents
   If ThisWorkbook.Sheets("mini sized DOW").Range("ar6") = myValue Then
      If myCount > 17 Then
            DoEvents
      ElseIf myCount = 17 Then
            '-- green highlite when over 17 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = 4
            Open "D:\temp\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 17 seconds"
            Close 1
      ElseIf myCount > 10 Then
            DoEvents
      ElseIf myCount = 10 Then
            '-- yellow highlite when over 10 seconds
            ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = 6
            Open "D:\temp\ABC.txt" For Append As #1  'Change path & name to suit
            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 10 seconds"
            Close 1
      End If
      myCount = myCount + 1
   Else
      myCount = 1
      ThisWorkbook.Sheets("mini sized DOW").Range("ar6").Interior.ColorIndex = xlNone
      myValue = ThisWorkbook.Sheets("mini sized DOW").Range("ar6")
   End If
   myTime = Now + TimeValue("00:00:01")
   Application.OnTime myTime, "StartTimer"
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:akoster
ID: 36586451
Oh, and be sure to change the file path back to

C:\Users\User\Documents\ABC.txt
0
 

Author Comment

by:cpatte7372
ID: 36586458
Brilliant mate.

That intervals are working.

Now, if you can have them logged to a text file, that would be fantastic.
0
 

Author Comment

by:cpatte7372
ID: 36586550
akoster,

Im excited.... going to check it out now ... I feel like a kid, ha ha....
0
 
LVL 19

Expert Comment

by:akoster
ID: 36586554
with lines 9-11 and 17-19, occurrances should be logged to a text file on the D drive in the temp folder.
0
 

Author Comment

by:cpatte7372
ID: 36586612
Its working like a charm.

Can I be a bit cheeky and ask if its possible to also get the value that it occurred at as well?

Cheers
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:cpatte7372
ID: 36586725
Hi akoster,

I tweaked the formula to highlight every 10s and 5s, however, it seems to highlight after every two seconds. Did I do something wrong?

Cheers
0
 

Author Comment

by:cpatte7372
ID: 36586760
So, the 5 seconds gives me and highlight in 2 seconds and the 10 seconds gives me a highlight in 5 seconds.

Maybe I ought to double the numbers to 10 and 20 seconds? :-)
0
 
LVL 19

Expert Comment

by:akoster
ID: 36586815
i'm not sure what went wrong, but it could be that somehow multiple timers are running at the same time, thereby effectively splitting the time needed in half.
Can you add this line
debug.Print now & " timer activated"

Open in new window


in between

Sub StartTimer()

and

DoEvents

?




0
 
LVL 19

Expert Comment

by:akoster
ID: 36586823
effect should be something like

23-9-2011 15:09:48 timer activated
23-9-2011 15:09:49 timer activated
23-9-2011 15:09:50 timer activated

but when you see something like this instead you know what's wrong
23-9-2011 15:09:56 timer activated
23-9-2011 15:09:56 timer activated
23-9-2011 15:09:57 timer activated
23-9-2011 15:09:57 timer activated
23-9-2011 15:09:58 timer activated
23-9-2011 15:09:58 timer activated
0
 

Author Comment

by:cpatte7372
ID: 36586878
I included the debug, and the highlight is going a bit crazy now. Highlighting every second...
0
 

Author Comment

by:cpatte7372
ID: 36586958
The timing appear completely out of sync. Maybe its my clock or something...??
0
 

Author Comment

by:cpatte7372
ID: 36587055
Hello Mate,

It looks like I just needed to restart excel. It appears to be fine now.

When you get a spare moment, if you could include the value that the time occurred as well in the text file that would be great.

Cheers
0
 
LVL 19

Expert Comment

by:akoster
ID: 36587153
Sure that would be as simple as

            Write #1, Format(Now, "dd-mm-yyyy hh:MM:ss.ss") & " Highlight 17 seconds, value =" & ThisWorkbook.Sheets("mini sized DOW").Range("ar6")

of course you can change AR6 to any arbitrarily chosen cell or named range.
0
 

Author Comment

by:cpatte7372
ID: 36587339
akoster,

I will insert that formula at lunch time.

Cheers mate.
0
 
LVL 19

Expert Comment

by:akoster
ID: 36587509
no problem. in 30 minutes i will be gone for the weekend so feel free to post any additional remarks but know that I will be able to answer them next monday.
0
 

Author Comment

by:cpatte7372
ID: 36588509
akoster,

It appears to be working like a dream.

You've made my weekend.

Cheers mate.
0
 

Author Closing Comment

by:cpatte7372
ID: 36593593
This guy is more than a wizard.

Can't thank this akoster enough
0
 
LVL 19

Expert Comment

by:akoster
ID: 36595805
you're welcome !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now