Additonal Excel Timer Alert and Logging

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
cpatte7372Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

cpatte7372Author Commented:
Experts,

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

Thanks in advance for you help.
0
cpatte7372Author Commented:
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
cpatte7372Author Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

cpatte7372Author Commented:
I take it back, I didn't work out the additonal timer of 10 seconds
0
Arno KosterCommented:
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
cpatte7372Author Commented:
Hi akoster, thanks for responding,

Going to check it out now...
0
cpatte7372Author Commented:
akoster,

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

Cheers
0
Arno KosterCommented:
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

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
Arno KosterCommented:
Oh, and be sure to change the file path back to

C:\Users\User\Documents\ABC.txt
0
cpatte7372Author Commented:
Brilliant mate.

That intervals are working.

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

Im excited.... going to check it out now ... I feel like a kid, ha ha....
0
Arno KosterCommented:
with lines 9-11 and 17-19, occurrances should be logged to a text file on the D drive in the temp folder.
0
cpatte7372Author Commented:
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
cpatte7372Author Commented:
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
cpatte7372Author Commented:
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
Arno KosterCommented:
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
Arno KosterCommented:
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
cpatte7372Author Commented:
I included the debug, and the highlight is going a bit crazy now. Highlighting every second...
0
cpatte7372Author Commented:
The timing appear completely out of sync. Maybe its my clock or something...??
0
cpatte7372Author Commented:
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
Arno KosterCommented:
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
cpatte7372Author Commented:
akoster,

I will insert that formula at lunch time.

Cheers mate.
0
Arno KosterCommented:
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
cpatte7372Author Commented:
akoster,

It appears to be working like a dream.

You've made my weekend.

Cheers mate.
0
cpatte7372Author Commented:
This guy is more than a wizard.

Can't thank this akoster enough
0
Arno KosterCommented:
you're welcome !
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 Development

From novice to tech pro — start learning today.