# Simple count up timer to monitor how long a cell value = 1

June 21, 2017 at 05:37:18
Specs: Windows 7
 I am trying to make a simple count up timer (hh:mm:ss) to monitor how long a cell value is 1 and stop when it is 0 that can be reset from another macro that I schedule to run at a certain time.I am not a programmer but am learning.Thanks

See More: Simple count up timer to monitor how long a cell value = 1

#1
June 21, 2017 at 06:31:40
 I am assuming this will be in Excel?This can be achived with vba, when the cell value reaches 0 should the timer value hold until you reset?How many cells are you monitoring for 0 and 1 values?

Report •

#2
June 21, 2017 at 08:01:48
 Sorry I should have been more clearWhen the cell value is 1 the timer will increaseWhen the cell value is 0 the timer will stopthe timer will accumulate until resetThis will be in ExcelThanks for the quick response

Report •

#3
June 21, 2017 at 10:25:40
 So, lets take cell A1 for example for the 1 or 0 value, and B1 for the timer.Ok say A1 = 1B1 will keep on incrementing in seconds until A1 becomes 0 again, is this correct?I'll have a play tomorrow

Report •

Related Solutions

#4
June 21, 2017 at 12:51:47
 Yes thats perfectI will need to call the sub reset from another macro but that everything Thanks

Report •

#5
June 22, 2017 at 01:50:26
 Ok, here is an attempt, you will need to add three buttons to sheet1 (make sure it is sheet1 for now)The buttons will need to be namedCmdStartCmdStopCmdResetCell C7 will have the value 1 or 0 (ensure it is 1 when you press start otherwise it wont start counting)Cell H7 will need to be formatted as time and the value 00:00:00 within itPaste the below code into sheet1 moduleEnter 1 in Cell C7 Press startYou can now either press Stop OR change the value in C7 from 1 to 0 and the count will stop and the time will be held in H7. It is not perfect but lets play around and see what we can do with it.```Dim StopTimer As Boolean Dim SchdTime As Date Dim CountTimer As Date Dim Etime As Date Const OneSec As Date = 1 / 86400# Private Sub CmdStart_Click() StopTimer = False SchdTime = Now() CountTimer = "00:00:00" Application.OnTime SchdTime + OneSec, "Sheet1.NextTick" End Sub Private Sub CmdStop_Click() StopTimer = True Beep End Sub Private Sub CmdReset_Click() StopTimer = True Etime = 0 Range("C7") = 1 Range("H7") = "00:00:00" End Sub Sub NextTick() If StopTimer Or Range("C7") <> 1 Then Else AppActivate Application.Caption SchdTime = SchdTime + OneSec CountTimer = CountTimer + OneSec Range("H7") = CountTimer Application.OnTime SchdTime, "Sheet1.NextTick" Etime = Etime + OneSec End If End Sub ```

Report •

#6
June 22, 2017 at 07:40:39
 Sorry I forgot to mention, This spread sheet runs completely untended, the 0 or 1 value is coming from a remote location

Report •

#7
June 22, 2017 at 08:13:16
 Depends on what you mean by "untended" and "remote location," but this is sounding more like you need a simple program, and not an Excel macro. AlwaysWillingToLearn: That timer's going to be fragile. You're reliant on "NextTick" always firing on time, which Windows doesn't guarantee and Excel has known periods where it won't execute VBA code. To do what you're attempting, you need more persistent information than you have. Ideally when the timer started, and the value of the timer at that time. EDIT: I feel VBA's second representation doesn't actually perfectly represent a second since it's using floating point math, so you can probably expect some drift in the timer. Same persistent information would solve that issue. message edited by Razor2.3

Report •

#8
June 22, 2017 at 08:25:17
 Hi Razor,I agree, it was not a perfect solution but in the absence of anything else i thought id try. I have used Excel timer on many project but only where accuracy isn't absolutely paramount. Otherwise i tend to use vb6, its old but works for me. Other than Excel vba i have no other solution.

Report •

#9
June 22, 2017 at 09:36:33
 It's not that it can't be done, but like I said, you need to keep track of when the timer was started. Then the math becomes easy. timerValue = (Now() - timerStart) + timerInitialValue. Then you don't need to worry about Application.OnTime, unless you want to update the display, and the impact from the inherent inaccuracy of floating point is minimized. The timer becomes event based instead of polling based, so the system performance technically improves. (Although things would have to be pretty bad for a timer to make a noticeable impact on performance.) The timer still technically runs if the workbook is closed, which is either a bug or a feature depending on how you sell it.

Report •

#10
June 25, 2017 at 03:50:15
 The Timer does not need to be atomically accurate even a few sec per hour is OK, This is how i am using the cell "=rslinx|'MCP04'!'Stop_PS_Lanes'" which is DDE from a PLC and I am trying to time how long the lanes are stopped.

Report •