Simple Timer facility

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

In a large Excel spreadsheet, I am looking to add a simple timer which will,
when started, put say a 'Y' in a cell for 1 hour. No countdown display or
anything complicated needed.

I've googled and tried a couple from the web (VBA code) but they seem to tie
up the spreadsheet's resources (hourglass spinning etc) while they are
running, which is no good for what I want.

Any suggestions please?

TIA,

V
 
Victor Delta said:
In a large Excel spreadsheet, I am looking to add
a simple timer which will, when started, put say a
'Y' in a cell for 1 hour. No countdown display or anything complicated
needed.
Click to expand...

Untested; there might be syntax errors.

Dim endtime as Double ' global to module

Sub startIt()
Range("A1") = "Y"
endTime = Now + TimeSerial(1,0,0) ' note: one-second resolution
Application.OnTime endTime, "stopIt"
End Sub

Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub

The OnError and OnTime/False statements allow stopIt to double as a way to
abort the time-out event before the 1-hour deadline, if you wish.

That is also the reason for putting the time-out deadline into a global
variable (endTime).
 
joeu2004 said:
Untested; there might be syntax errors.

Dim endtime as Double ' global to module

Sub startIt()
Range("A1") = "Y"
endTime = Now + TimeSerial(1,0,0) ' note: one-second resolution
Application.OnTime endTime, "stopIt"
End Sub

Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub

The OnError and OnTime/False statements allow stopIt to double as a way to
abort the time-out event before the 1-hour deadline, if you wish.

That is also the reason for putting the time-out deadline into a global
variable (endTime).
Click to expand...

Brilliant, that seems to do the job perfectly! Very many thanks.

Is there any way to force 'stopIt' if the spreadsheet is closed during the 1
hour?

V
 
Victor Delta said:
joeu2004 said:
Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub
Click to expand...
[....]
Is there any way to force 'stopIt' if the spreadsheet
is closed during the 1 hour?
Click to expand...

You could create the following Workbook_Close event macro (untested; beware
of syntax errors):

Sub Workbook_Close()
stopIt
End Sub

The Workbook_Close event macro goes in the Workbook object, not in a
worksheet or normal module. To ensure proper syntax of the declaration,
click on the pull-down menus at the top of the VBA editing pane.

That paradigm presumes that stopIt is in a normal module (not a worksheet
object), and it is not Private.

Of course, there is no need to stop the timer. That should be done
automagically by Excel.

But I presume you want to clear A1 (in my example).
 
joeu2004 said:
Victor Delta said:
joeu2004 said:
Sub stopIt()
Range("A1").ClearContents
On Error Resume Next
Application.OnTime endTime, "stopIt",, False
End Sub
Click to expand...
[....]
Is there any way to force 'stopIt' if the spreadsheet
is closed during the 1 hour?
Click to expand...

You could create the following Workbook_Close event macro (untested;
beware of syntax errors):

Sub Workbook_Close()
stopIt
End Sub

The Workbook_Close event macro goes in the Workbook object, not in a
worksheet or normal module. To ensure proper syntax of the declaration,
click on the pull-down menus at the top of the VBA editing pane.

That paradigm presumes that stopIt is in a normal module (not a worksheet
object), and it is not Private.

Of course, there is no need to stop the timer. That should be done
automagically by Excel.

But I presume you want to clear A1 (in my example).
Click to expand...

Thanks. Following your guidance on the pull-down menus, I've ended up with:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Run "stopIt"

End Sub

which seems to work well.

Previously I found that if you closed and then reopened the spreadsheet, the
timer was still running which was not what I wanted in that situation. (I've
also set up buttons to start and stop the timer.)

Thanks again,

V
 
Victor Delta said:
Following your guidance on the pull-down menus,
I've ended up with: Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stopIt"
End Sub
which seems to work well.
Click to expand...

You're probably right: BeforeClose instead of Close. I had not looked or
tested to see when each event occurs.

However, there really is no need to use Run "stopIt". It is inefficient.
Simply stopIt or Call stopIt should work just fine.


Victor Delta said:
Previously I found that if you closed and then reopened
the spreadsheet, the timer was still running which was
not what I wanted in that situation.
Click to expand...

I wondered about that myself, and I intended to test it. Thanks for the
heads-up. I am a little surprised; but the operative word is "little" ;-).
 
joeu2004 said:
You're probably right: BeforeClose instead of Close. I had not looked or
tested to see when each event occurs.

However, there really is no need to use Run "stopIt". It is inefficient.
Simply stopIt or Call stopIt should work just fine.




I wondered about that myself, and I intended to test it. Thanks for the
heads-up. I am a little surprised; but the operative word is "little"
;-).
Click to expand...

Amended it. Thanks.

V
 
Back
Top