Display constanlty updating time in a cell
 

Hi,

I am using Excel 2007. I would like to configure a cell so that it will
display the current time. If feasible, I would like this cell to be
automatically updated once per second. I can set the cell formula =Now(),
but this does not get automatically updated. So far, the only way I have
come up with is to create a Sub and use the Application.OnTime function to
call it once per second. (I called this Sub pkPeriodic and I listed it
below).

Anyway, my pkPeriodic Sub does the job, but it makes the user experience
terrible. It is very noticeable that my script is running €“ you can see the
cursor briefly disappear/reappear once per second. This seems to briefly
take control away from the user to the extent that it makes it difficult to
for him/her to use the worksheet.

If anyone can think of a better way I can do this, I would really appreciate
it.

Thanks,
Paul

PS..Here is my Sub pkPeriodic()

Public Sub pkPeriodic()

Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
Procedu="pkPeriodic"
wksParams.Range("CurTime") = Time
End Sub

Display constanlty updating time in a cell
 

hi
see this site...
http://www.mvps.org/dmcritchie/excel/datetime.htm

it a large site, all about dates, time, funcntions about times/dates etc.
good site
but you will be looking for the real time clock towards the bottom.

regards
FSt1

"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I would like to configure a cell so that it will
display the current time. If feasible, I would like this cell to be
automatically updated once per second. I can set the cell formula =Now(),
but this does not get automatically updated. So far, the only way I have
come up with is to create a Sub and use the Application.OnTime function to
call it once per second. (I called this Sub pkPeriodic and I listed it
below).

Anyway, my pkPeriodic Sub does the job, but it makes the user experience
terrible. It is very noticeable that my script is running €“ you can see the
cursor briefly disappear/reappear once per second. This seems to briefly
take control away from the user to the extent that it makes it difficult to
for him/her to use the worksheet.

If anyone can think of a better way I can do this, I would really appreciate
it.

Thanks,
Paul

PS..Here is my Sub pkPeriodic()

Public Sub pkPeriodic()

Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
Procedu="pkPeriodic"
wksParams.Range("CurTime") = Time
End Sub

Display constanlty updating time in a cell
 

Thanks FSt1 -

The method on the dmcritchie site is pretty much the same as the method I
was using (using the Application.OnTime function to call a Sub that updates
the cell once per second). dmcritchie has a warning against doing this
because it steals cycles and causes a noticeable slowdown - this is exactly
the problem I was experiencing. It is good to know that someone more
knowledgeable than I am has noticed the same thing.

The slowdown is too noticeable for what I am trying to do. As an
alternative, I have found a DDE server that has a tag for the system time. I
have started experimenting with this and my time is updating once per second
and I do not notice any noticeable slowdown for the user. I am very pleased
so far. I don't know if I will run into any problems as I progress
further...but I will see.

Thanks again for your help,
Paul


--
Paul Kraemer


"FSt1" wrote:

hi
see this site...
http://www.mvps.org/dmcritchie/excel/datetime.htm

it a large site, all about dates, time, funcntions about times/dates etc.
good site
but you will be looking for the real time clock towards the bottom.

regards
FSt1

"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I would like to configure a cell so that it will
display the current time. If feasible, I would like this cell to be
automatically updated once per second. I can set the cell formula =Now(),
but this does not get automatically updated. So far, the only way I have
come up with is to create a Sub and use the Application.OnTime function to
call it once per second. (I called this Sub pkPeriodic and I listed it
below).

Anyway, my pkPeriodic Sub does the job, but it makes the user experience
terrible. It is very noticeable that my script is running €“ you can see the
cursor briefly disappear/reappear once per second. This seems to briefly
take control away from the user to the extent that it makes it difficult to
for him/her to use the worksheet.

If anyone can think of a better way I can do this, I would really appreciate
it.

Thanks,
Paul

PS..Here is my Sub pkPeriodic()

Public Sub pkPeriodic()

Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
Procedu="pkPeriodic"
wksParams.Range("CurTime") = Time
End Sub