Problems with running Clock in worksheet cell

[Jaafar Tribak]

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.

 

[RoryA]

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.

 

[Jaafar Tribak]

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 :

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 :

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.

 

[Jaafar Tribak]

Forgot the workbook demo : see here

Regards.

 

[RoryA]

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...

 

[RoryA]

Works in 2007 SP1 on Vista HP too.

 

[Colin Legg]

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?

 

[RoryA]

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!

 

[Jaafar Tribak]

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?

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

Regards.

 

[RoryA]

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!