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

Reply ↓  Report •

#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?


Reply ↓  Report •

#2
June 21, 2017 at 08:01:48
Sorry I should have been more clear
When the cell value is 1 the timer will increase
When the cell value is 0 the timer will stop
the timer will accumulate until reset
This will be in Excel
Thanks for the quick response

Reply ↓  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 = 1

B1 will keep on incrementing in seconds until A1 becomes 0 again, is this correct?

I'll have a play tomorrow


Reply ↓  Report •

Related Solutions

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

Reply ↓  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 named

CmdStart
CmdStop
CmdReset

Cell 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 it

Paste the below code into sheet1 module
Enter 1 in Cell C7
Press start

You 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



Reply ↓  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

Reply ↓  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.

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  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.


Reply ↓  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.

How To Ask Questions The Smart Way


Reply ↓  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.

Reply ↓  Report •

Ask Question