The NOW function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Usually the NOW function does not update until you save or re-open the file
or press calculate (F9).

However I somehow managed to get it to update constantly (so I could have a
countdown clock running). Does anyone know how to do this?

Thanks
Atreides
 
Thanks Anne, almost there..

That bit of code sets up a periodically running subroutine... but what to
put in the subroutine? How can I make it so that a specific cell always shows
the instantaneous current time?

Peter
 
Dim nTime As Double
Sub myClock()
nTime = Now + TimeSerial(0, 0, 1) ' 1 secs
Worksheets("Sheet1").Range("A1").Value = Time
Application.OnTime nTime, "myClock"
End Sub

Sub StopClock()
Application.OnTime nTime, "myClock",,False
End Sub

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks Bob, that looks promising, however I'm not sure exactly how to run
this code. I assume that I need a sheet labelelled "Sheet1" and that the
clock will appear in cell A1? Perhaps I've entered the code wrong.My VBA
module looks like this:

====

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"
Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()

Dim nTime As Double
Sub myClock()
nTime = Now + TimeSerial(0, 0, 1) ' 1 secs
Worksheets("Sheet1").Range("A1").Value = Time
Application.OnTime nTime, "myClock"
End Sub

Sub StopClock()
Application.OnTime nTime, "myClock", , False
End Sub

StartTimer

End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

====

Should I have put your block somewhere else?

Thanks
Atreides
 
You don't need anything on the worksheet, it finds A1 itself (you can change
that to any cell you want).

All you need to do is fire it. Goto menu Tools>Macro>Macros... and select
StartTimer from the list, then click Run. You can stop it in a similar
manner.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks Bob, it works!

I'm really new to Macros so I didn't know you could just run them like that.
I only used them to write functions before.
 
A whole new world is opening to you <G>

Bob

Atreides said:
Thanks Bob, it works!

I'm really new to Macros so I didn't know you could just run them like that.
I only used them to write functions before.
Click to expand...
 
Back
Top