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.

 

[Colin Legg]

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!

Yes, using offset or cells properties to refer to other cells and change their acutal values seems to be fine as well?

Private Sub TimerProc()
 
    On Error Resume Next
    oTargetCell.Calculate
    oTargetCell.cells(2,2).Value = "abc abc"

End Sub

The error handling is important - if an unhandled error in TimerProc() is raised and causes debug mode to be entered then it's curtains.

 

[Jaafar Tribak]

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!

Actually, that was what i was thinking of doing because when first tackling the clock problem, i was thinking of doing it from a new excel instance via automation.

I remember, needing this in a project before but was stuck when the server excel application was in Edit mode.

Regards.

 

[RoryA]

I'll be very interested if you can do that!

 

[RoryA]

Yes, using offset or cells properties to refer to other cells and change their acutal values seems to be fine as well?

Private Sub TimerProc()
 
    On Error Resume Next
    oTargetCell.Calculate
    oTargetCell.cells(2,2).Value = "abc abc"
 
End Sub

particularly surprising that that doesn't crash even if you happen to be editing the "abc abc" cell! Now I'm excited again...

 

[Jaafar Tribak]

particularly surprising that that doesn't crash even if you happen to be editing the "abc abc" cell! Now I'm excited again...

This seems to be opening up some interesting new possibilities Am i getting too excited here

Regards.

 

[RoryA]

One word of warning - don't stick a MsgBox statement in the TimerProc....
Quite impressive having multiple messageboxes appear though!!

 

[Colin Legg]

I suspect that in finance it might be more useful. (over to you, Colin! )

I "need" hard and fast rules on what will cause a crash and what won't, and that would require extensive and pain-staking testing. It could be pretty cool but not if it causes inexplicable crashes!

 

[Jaafar Tribak]

I "need" hard and fast rules on what will cause a crash and what won't, and that would require extensive and pain-staking testing. It could be pretty cool but not if it causes inexplicable crashes!

AFAIK, if you just stick an error hadler or just 'On Error Resume next' inside the timer callback , windows timers are quite stable and will not crash the application even if the worksheet is edited via code or via the UI.

Regards.

 

[Jaafar Tribak]

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!

Here is something close.

I have put together this function that will guarantee that excel responds to automation calls and more importantly prevents causing an exception if the server application happens to be in edit mode.

The idea is to force the server application out of exit mode but without needing to activate it so everything goes on discretely behind the scenes.

here is an example :

In a standard module

[COLOR=seagreen]'////////////////////////////////////////////////////////[/COLOR]
[COLOR=seagreen]'Routine that checks if a target instance of Excel[/COLOR]
[COLOR=seagreen]'is currently in edit mode and if so,force it[/COLOR]
[COLOR=seagreen]'to exit the edit mode in time, to make it capable[/COLOR]
[COLOR=seagreen]'of responding to automation calls from the current app[/COLOR]
[COLOR=seagreen]'without code causing exceptions and crashing.[/COLOR]
[COLOR=seagreen]'////////////////////////////////////////////////////////[/COLOR]
 
Option Explicit
 
Private Declare Function FindWindowEx Lib "user32" Alias _
"FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
 
Private Declare Function ShowWindow Lib "user32" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
 
Private Declare Function IsIconic Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function GetWindowLong Lib "user32" Alias _
"GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
 
Private Declare Function PostMessage Lib "user32" Alias _
"PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Const GWL_STYLE As Long = -16
Private Const WS_VISIBLE As Long = &H10000000
Private Const VK_ESC As Long = &H1B
Private Const WM_KEYDOWN As Long = &H100
Private Const SW_SHOWNOACTIVATE = 4
Private Const SW_SHOWNA = 8
Private Const SW_HIDE = 0
 
Function MakeSureApplicationIsReady _
(ServerApplication As Application) As Boolean
 
    Dim lXLDESKhwnd As Long, lXL6hwnd As Long
    Dim lCurrentStyle As Long
 
    [COLOR=seagreen]'/make sure the server app is a different excel instance.[/COLOR]
    If Application.hwnd <> ServerApplication.hwnd Then
 
        [COLOR=seagreen]'/if so,get its workbooks desktop hwnd.[/COLOR]
        lXLDESKhwnd = FindWindowEx _
        (ServerApplication.hwnd, 0, "XLDESK", vbNullString)
 
        [COLOR=seagreen]'/find the servers's editor window hwnd.[/COLOR]
        lXL6hwnd = FindWindowEx _
        (lXLDESKhwnd, 0, "EXCEL6", vbNullString)
 
        [COLOR=seagreen]'/now retrieve servers's editor current window styles.[/COLOR]
        lCurrentStyle = GetWindowLong(lXL6hwnd, GWL_STYLE)
 
        [COLOR=seagreen]'/check if the visible style is set-[/COLOR]
       [COLOR=seagreen]'/meaning the app is in edit mode.[/COLOR]
        If lCurrentStyle And WS_VISIBLE Then
            Debug.Print "Edit mode."
 
           [COLOR=seagreen]'/if the server app is in edit mode,exit the edit mode[/COLOR]
            [COLOR=seagreen]'/by sending to it a discreet 'esc' key stroke without[/COLOR]
            [COLOR=seagreen]'/activating it.[/COLOR]
            With ServerApplication
                Select Case True
                    Case IsIconic(.hwnd)
                        ShowWindow .hwnd, SW_HIDE
                        ShowWindow .hwnd, SW_SHOWNOACTIVATE
                        PostMessage .hwnd, WM_KEYDOWN, VK_ESC, 0
                        ShowWindow .hwnd, SW_HIDE
                        ShowWindow .hwnd, SW_SHOWNA
                    Case Else
                        PostMessage .hwnd, WM_KEYDOWN, VK_ESC, 0
                End Select
            End With
        Else
            Debug.Print "Ready mode."
        End If
 
        [COLOR=seagreen]'/return True to indicate edit mode is now cancelled and[/COLOR]
        [COLOR=seagreen]'/automation can now be safely performed on the server app.[/COLOR]
        MakeSureApplicationIsReady = True
    End If
 
End Function

here is an example of how to call the function:

[COLOR=seagreen]'////////////////////////////////////////////////////////[/COLOR]
[COLOR=seagreen]'open a new instance of excel,add a new workbook,save it[/COLOR]
[COLOR=seagreen]'in the same folder as that of this workbook and give it[/COLOR]
[COLOR=seagreen]'the name of 'server.xls'.[/COLOR]
[COLOR=seagreen]'with the new server workbook open,enter EDIT mode[/COLOR]
[COLOR=seagreen]'and run the code below to demonstrate that the server[/COLOR]
[COLOR=seagreen]'edit mode is cancelled in time so it can respond to[/COLOR]
[COLOR=seagreen]'automation calls without causing an exception.[/COLOR]
[COLOR=seagreen]'///////////////////////////////////////////////////////[/COLOR]
 
Option Explicit
 
Sub Test()
 
    Dim oWb As Workbook
 
   [COLOR=seagreen]'/////////////////////////////////////////////[/COLOR]
 
    [COLOR=seagreen]'establish a reference to the wkb 'Server.xls'[/COLOR]
[COLOR=seagreen]  'this assumes the wkb 'Server.xls' is located[/COLOR]
[COLOR=seagreen]  'in the same folder as that of this workbook[/COLOR]
[COLOR=seagreen]  'and it is currently open in a second instance[/COLOR]
[COLOR=seagreen]  'of excel.[/COLOR]
 
   [COLOR=seagreen]'/////////////////////////////////////////////[/COLOR]
 
 
    Set oWb = GetObject(ThisWorkbook.Path _
    & Application.PathSeparator & "Server.xls")
    If MakeSureApplicationIsReady(oWb.Parent) Then
        Range("a1") = oWb.Worksheets(1).Range("a1")
        MsgBox "Excel responded to automation calls."
    End If
 
End Sub

Obviously, this hack wont work with a remote excel server.

Also, if the excel server has a modal dialog on display while making an automation call ,the client application will hang but i guess this problem can also be worked-around in a similar way.

I hope this works accross diiferent versions. tested on WIN XP, excel 2003.

Regards.

 

[tusharm]

People have! {grin}

Everytime I've tested the SetTimer/KillTimer approach it has crashed Excel if the callback code is executed while Excel is in edit mode. Of course, all those scenarios involved more than the recalculation of one cell. One example:
How do I close a workbook that hasn't been used in 5 minutes? (works in edit mode)
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=8

One thought. What if you are editing the cell that is being recalced? Does that affect Excel's behavior?

Another thing you might want to check is if something like the code below works. It's from
How can I make a cell (or some other object) flash?
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=14

ActiveWorkbook.Styles("normal").NumberFormat = _
        ActiveWorkbook.Styles("normal").NumberFormat

I

{snip}

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

Regards.