Timer please

[Juriemagic]

Hi good people!,

Please help me, I have tried many answers from other forums, tried to adapt, but get errors like, "code not compatible with 64 base", and many other vba errors. Please, all I need is when cell A1=1, I want a simple timer to start counting elapsed time, indicating Hours, minutes and seconds. I know excel timer functions is not 100% accurate, but that is okay. The seconds can be in integers only, that's okay. And then when the cell goes to 0 value, the timer must stop. NOT reset. Maybe a button should be used to reset the timer?..Please help, I will be very grateful...

[Yongle]

Please tell us what you have already tried and has not worked, including error messages/numbers
thanks

[Yongle]

Cross-posted here (http://www.mrexcel.com/forum/excel-questions/840614-timer-please.html)

[Juriemagic]

Yes I have cross-posted. I included the link to mr excel, but because of rules the post was not excepted since I am still a newbie on this forum. So what is a man to do then?

[Yongle]

Very puzzled by your reply
- Where did you include the link to MR Excel?
- According to Mr Excel you are a Board Regular since May 2014 - not a newbie

But everyone deserves a 2nd chance
Please tell us what you have already tried and has not worked, including the specific error messages/numbers
thanks

[Juriemagic]

On mr excel I'm not a newbie, but on this forum I am, therefore cannot attach links. Unless this exclusion is not part of VBA Express forum rules, in which case I will not understand why links are not allowed. I included the link on the post, in other words I wrote:..I have posted this on Mr excel some time ago, but have to date not received any response. The thread can be found by clicking this link: "here I added the link". When I submitted the thread, a message came up stating that the thread was rejected. So I took away the link, and the thread went through.

Anyways, I have found some codes on the net, copied and pasted in the module sheets, assigned them to form buttons, but all sorts of errors came up, or the codes just simply did not work. To get the specific messages I will have to go back on that path, which I think I will do. I will then record the messages, and include the codes I get, so then hopefully you will kindly help me to make the necessary changes to suit my specific need?. I will greatly appreciate any help you may be able to give...I will come back to this thread a bit later once I have more to work with..thanx for your time so far..

[Juriemagic]

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long
Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long
'~~> Start Timer
Sub StartTimer()
'~~ Set the timer for 1 second
TimerSeconds = 1
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub
'~~> End Timer
Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub
Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'~~> Update value in Sheet 1
Sheet1.Range("A1").Value = Time
End Sub



Okay, for example, the code above I got from a site, tried and tested it says...yet, when I copy and paste into a module on my sheet, the top section highlights red, If I dare remove the top section, an error comes up. So I am wondering how on earth this code worked for the person who actually posted the thread?. And so I can go on and on, without finding something I can work with...Would you please be so kind to check this code and see why it's not working?..Thank you Yongle..

[Yongle]

Ok - have never done this before, but this seems to work fine.
I just copied code from http://smallbusiness.chron.com/create-timer-excel-29834.html and modified a few lines

Timer is in cell B1 and is dependant on value in cell A1
I have attached workbook including code and it does exactly what you seem to want.
To run the code ctrl + shift + c
Whilst it is running, change the value in cell A1 to zero and timer stops
(in fact anything other than 1 will stop the timer)
Change the value back to 1 again and re-run the macro and it starts from where it stopped.

You can now modify it to suit your needs


Sub Counter()
Dim count As Range
If Range("A1") = 1 Then
Range("B1").NumberFormat = "mm ss"
Set count = [B1]
count.Value = count.Value - 1.1574074074074E-05
Call RunTime
Else
End If
End Sub

Sub RunTime()
Dim CD As Date
CD = Now + TimeValue("00:00:01")
Application.OnTime CD, "Counter"
End Sub

[Yongle]

I am puzzled - post#7 was not on my screen when I posted post#8

I will look at your code in post#7 later

[Juriemagic]

No worries,I have found a clock which I can manipulate to give me elapsed time. Thank you very much for your time spent on this, I really appreciate it. Have a nice day..

[Juriemagic]

I have tried your code, it works but it counts down. I needed something that would start at 0 seconds, start counting when something happens, and stop counting when that something stops. That way I would be able to see how long a specific task took to execute. Thank you anyways for your time..

[Yongle]

This code times how long a segment of your vba takes to run, adding to the previous cumulative time . It reports it in cell B1.
(I have not included a clock that counts on screen - which complicates the coding without telling you anything more and (marginally) slows things down).

How it works
Timer - is a very useful vba function which counts the number of seconds since midnight.
So take the value of Timer "before procedure" away from Timer "after procedure" and that gives you the number of seconds the procedure took to run
Counter - is the cumulative time taken previously and is added to the time the current procedure takes to run to arrive at our latest cumulative value.
(It is declared as a public variable so that the value can be retained to the next procedure. It will reset when you close the workbook. )
What you need to do
Just put some code that takes a while to execute where it says PUT YOUR VBA CODE IN HERE and give it a try.If you run it a few times you will see that the values are retained etc.
Hopefully this gives you the information you need to capture.



Option Explicit
Public Counter As Long ' Value retained if declared as public
Sub Time_VBA()
'Variables
Dim StartTime As Double, EndTime As Double, ElapsedTime As Double, CumulativeTime As Double
'The code that does the work

StartTime = Timer
' PUT YOUR VBA CODE IN HERE

EndTime = Timer
ElapsedTime = EndTime - StartTime
CumulativeTime = CumulativeTime + ElapsedTime
Counter = Counter + ElapsedTime 'this carries into next macro
Range("B1") = Counter



End Sub

[Juriemagic]

Hahaha..yep, that's me..rather impatient boy!!..I thought that was something to smile about.. :-). I will definitely take your code, add mine, and test. Will let you know during the course of the day. Thanx a million!!

[Juriemagic]

Yongle, I am sure your code works perfectly. I just cannot get everything to work together when I insert my code. I really am tired of this now, I struggle only to a point, then I leave it. And believe me, I have been struggling a lot...Please know that I honestly appreciate the time and efforts you sacrificed to help me. I wish you a very blessed day!!

[jonh]

Private tmr_tm As Date
Private tmr_src As Range
Private tmr_dst As Range

Private Sub Worksheet_Change(ByVal Target As Range)
If tmr_src Is Nothing Then Set tmr_src = Range("a1") '<-- cell to check
If tmr_dst Is Nothing Then Set tmr_dst = Range("a2") '<-- time display
If Target.Address = tmr_dst.Address Then Exit Sub
If Target.Address = tmr_src.Address Or tmr_tm = 0 Then tmr_tm = Now
tmr
End Sub

Private Sub tmr()
Do
If tmr_src = 0 Then Exit Sub
tmr_dst = "'" & Format(Now - tmr_tm, "hh:mm:ss")
DoEvents
Loop
End Sub

[Yongle]

@Jonh - I cannot make your code run no matter what I do. Please supply the magic dust....
thanks

[jonh]

Copy the code into the worksheet module and edit cell a1.

[Yongle]

@Jonh

Still puzzled. This is the vba screendump. Have I entered in the correct way.
Nothing happens when I edit cell A1

thanks

13008

[jonh]

I'm not at my pc so I can't check, but it worked for me yesterday.
Have you debugged it?
It should just run the loop whenever a cell is changed if a1 is any value except 0.

[Yongle]

@Jonh -
It is probably my fault. But the code does nothing at all. Is it possible to debug code when it seems to be doing nothing at all. If so, what do I press - spell it out as if I know nothing - I have never debugged my code - other than via message boxes. I think the code has gone on strike and I have no idea why! What is the trigger to kick the code into action? Sorry to be so hopeless.
thanks

[Kenneth Hobs]

You need to move the code to the object Sheet1, not ThisWorkbook.

[Yongle]

@Kenneth Hobs
@JonH
Ah ha - having moved the code, time does indeed visibly march on in sheet1
But why does the code only work if placed in object Sheet1?
thanks

[Kenneth Hobs]

Because you are watching for changes on the sheet, not the workbook.

[Yongle]

Because you are watching for changes on the sheet, not the workbook.

@Kenneth Hobs
When you say it like that, it is a bit obvious really - but it did not seem so obvious at the time. I must try to engage brain a bit more laterally. I am very new to this...that's my excuse (I do not know how long I can keep on using that line before it starts wearing a bit thin..)
Thank you

[Juriemagic]

Hi John, Thank you a million times over!!!..I have pasted this code and it works absolutely brilliantly!!..I really do appreciate. Have a great day!!!

Yongle, you put the code in "this workbook". I think that might be the problem?..I put it in the sheet module and all works great...thanx for all your time as well..