Using Current Time

[Gavin Philpott]

Hello Everyone,

I'm trying to create a sheet in which calculations & conditional formats are
made according to comparisons with inserted (fixed) times & the current
(system clock) time.

I've used the NOW() function to get the current time into the sheet but this
does not automatically update as the time changes. Is there a way of
inserting a current time that updates, alternatively can a manual update
(F9) be scheduled to be done at regular intervals (once or twice a minute
would be enough for what I'm doing).

I'm running Excel 2002 on Win XP Home

Thanks in advance,

Gavin Philpott

 

[Jeff Kulp]

-----Original Message-----
Hello Everyone,

I'm trying to create a sheet in which calculations & conditional formats are
made according to comparisons with inserted (fixed) times & the current
(system clock) time.

I've used the NOW() function to get the current time into the sheet but this
does not automatically update as the time changes. Is there a way of
inserting a current time that updates, alternatively can a manual update
(F9) be scheduled to be done at regular intervals (once or twice a minute
would be enough for what I'm doing).

I'm running Excel 2002 on Win XP Home

Thanks in advance,

Gavin Philpott


.

Gavin,
The only way I know to do that would be using vba. Open
your spreadsheet and then hit alt + f11, this will open
vba. On the left double click the sheet you want to
calculate intermittently. On the upper right there are
two drop-downs. The left needs to be "Worksheet" and the
other needs to be "Change". A sub procedure will be
created, paste the follwong code, Worksheets
("sheet1").Calculate . Change "sheet1" to the name of
your worksheet and it will calulate the sheet everytime
there is a change. However this code will only run when
there is a change to the sheet.

Jeff

 

[Dave Peterson]

Chip Pearson has some notes on OnTime macros at:
http://www.cpearson.com/excel/ontime.htm

 

[Gavin Philpott]

Thanks for info

I have tried the Windows API method detailed; this works ok with the command
"calculate" inserted as the timer related code. However, once the timer is
running if you enter edit mode (i.e. try to type anything into any cell)
Excel closes without saving or error message (rather anoying as I want to
edit the sheet and insert values & time stamps with the timer running)

Any ideas? (I must confess I know absolutely nothing about VBA; I've just
blagged it this far using the code on the website & taking the code
generated from a recorded macro of an update to get the 'calculate' code)

Thanks to anyone who can help

Gavin.

 

[Dave Peterson]

I think I would have just tried the VBA method to start:

I added all this to a General module:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "MyCalculate"
Sub auto_open()
Call StartTimer
End Sub
Sub auto_close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
Sub mycalculate()
'Application.CalculateFull
Application.Calculate
StartTimer 'get ready for next time
End Sub

For testing purposes, I put =now() in a cell and gave it a custom format of:
hh:mm:ss (so I could see the seconds).

Then I changed the 120 seconds to 3 and started the timer.

The auto_open means that it'll start when the workbook closes and the auto_close
means that it'll stop the timer if you close that workbook.

(It doesn't sound like you're using this as an expensive clock (price of the
pc+excel), but this may cause a performance hit if want to do other stuff, too.)

 

[Gavin Philpott]

That worked perfectly.

Thanks for all you help.

Gavin.