Problems with running Clock in worksheet cell

Jaafar Tribak

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,664
Office Version
  1. 2016
Platform
  1. Windows
Greetings,

I've searched the board and this question has been raised before however, unless i overlooked it, no solution has been provided for the two following main problems caused by having a running clock in a worksheet cell :
1-Clock stops updating while excel is in EDIT MODE
2-Excel losing the UNDO functionality.

Anybody seen these issues addressed somewhere?

Regards.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
For a timer in a cell, there's nothing you can do about 1 since Excel won't run VBA while in Edit mode and will not update any other cells either.
For 2, you would have to create your own Undo stack I think.
If the clock were not in a cell, you might be able to work around 2.
 
Upvote 0
Well, I too thought this was difficult if not impossible to achieve but it turned out to be surprisingly easy !!

After a little trial & error testing, I've actually achieved both goals namely keeping the UNDO fuctionality and making the clock update even while in EDIT MODE !!!!

The trick is to assign a Now() formula and update the formula using the Calculate Method of the Range object inside a timer callback -That's all !

I don't exactly understand why this works - Worth looking into this more closely thogh to learn a few things.

Code in a standard module :

Code: 
Option Explicit
 
Private Declare Function SetTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerfunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32" _
(ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long
 
Private lTimerId As Long
Private oTargetCell As Range

Private Sub TimerProc()
 
    On Error Resume Next
    oTargetCell.Calculate
 
End Sub
 
Sub AddClock(Cell As Range)
 
    Set oTargetCell = Cell
    Cell.FormulaR1C1 = "=TEXT(NOW(),""hh:mm:ss"")"
    lTimerId = SetTimer(0, 0, 100, AddressOf TimerProc)
 
End Sub
 
Sub RemoveClock(Cell As Range)
 
    KillTimer 0, lTimerId
    Cell.FormulaR1C1 = vbNullString
 
End Sub


Now, simply call the AddClock/RemoveClock routines as follows :

Code: 
Option Explicit
 
Sub CreateClock()
 
    AddClock Range("a1")
    
End Sub

Sub DestroyClock()
 
    RemoveClock Range("a1")
 
End Sub

Tested in excel2003 french version on Win XP- Not sure if this works for other versions.

Regards.
 
Upvote 0
Yep - tested in English Excel 2003 (SP2) on WinXP and it works fine! Doesn't even seem to slow down the sheet particularly.
That's very interesting! I knew you could use the Timer to bypass the limitations on functions in worksheet cells, but I didn't believe it would work in Edit mode. That makes things quite exciting - thanks for that! :)
(Just goes to prove the old adage about saying "it can't be done" in a web forum...)

Edit: I just realised I said that was exciting - I may have crossed a line into uber-geekdom now... :biggrin:
 
Last edited:
Upvote 0
Works in 2007 SP1 on Vista HP too.
 
Upvote 0
From what I can see, this seems to be very solid, so well done Jafaar!

In my (very limited) experiences with using windows timers, I always found that updating a cell whilst in edit mode caused a crash. Obviously that's not always the case, which is interesting?

The only related links I could find with using Windows timers to bypass edit mode were at Chip Pearson's site (where he warns of the above) and at XVBT (link to Chip's site in this thread) where it was considered as a workaround to retrieving a cell's value whilst in edit mode (and I remember at the time it most definitely could cause a crash). I tried a few variations on the one in this post and it seemed to stay solid - I wonder what the definitive factor is as to whether or not a crash occurs?
 
Upvote 0
I wondered about that - I suspect it might be to do with the fact that the code is not directly changing the cell's value: it's simply making Excel recalculate it?
It also doesn't seem to be a problem if you have other cells that refer to A1 - they update nicely too.
Edit: it can make editing code tricky unless you turn the timer off though! ;)
 
Last edited:
Upvote 0
I
wondered about that - I suspect it might be to do with the fact that the code is not directly changing the cell's value: it's simply making Excel recalculate it?
Click to expand...

That's exactly what happens and it also saves the UNDO command which i never thought it would be possible given the fact that running a Macro that changes a range object ALWAYS causes the Undo functionality to be lost !!

So the trick lies in calculating the sheet inside a timer and not changing the range object directly.

Given the simplicity of this solution, I am surprised nobody thought about it before :confused:

Regards.
 
Upvote 0
I guess there's not a huge demand for clocks in cells? ;)
My initial excitement has waned a bit as I can't think of many situations offhand where I would need automatic timed recalculations, but I suspect that in finance it might be more useful. (over to you, Colin! :))
Now if you can figure a way to make Excel respond to automation calls even if it's in Edit mode, that might make a few people happy!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Links to cells in other Workbooks occasionally change the WORKSHEET reference. Why?
2
Replies
11
Views
288
GTS
G
C
  • Question
Custom Ribbon Strange Behaviour in Shared Workbook Saved in Maximized Window Mode
Replies
0
Views
362
coolvi
C
G
  • Solved
VBA to allow grouping +- after reopening of the sheet
Replies
7
Views
937
Greenbehindthecells
G
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
398
Nick Loughton
N
M
  • Question
VBA Run-time error ‘438’ - Unknown Reason
Replies
2
Views
963
rlv01
R

Forum statistics

Threads
1,216,747
Messages
6,132,487
Members
449,729
Latest member
davelevnt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top