Solved

Additonal Excel Timer Alert and Logging

Posted on 2011-09-21
28
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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:Arno Koster
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:
Arno Koster 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:Arno Koster
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:Arno Koster
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
 

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:Arno Koster
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:Arno Koster
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:Arno Koster
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:Arno Koster
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:Arno Koster
ID: 36595805
you're welcome !
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enabling the Skype for Business Meeting Scheduler in Hybrid OWA
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

691 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