A recursive procedure

[corentint]

Hello everyone and greetings!

I have concocted a procedure to control two (2) clocks for a chess game. On a sheet, I have 2 cells that contains the cumulative time spent by each player; I also have 2 buttons that each player must push in turn once their move is completed. Pushing one button activate the clock of the opponent while stopping the other clock.

All the code is in the sheet module (object).

Here is the code:
----------------------------------------------------------------------------------------------
Sheet1(Board)(Code)
Dim Clocks$, NextTick

Sub StartBlackClock()
'AND STOP THE WHITE CLOCK!

'Depress black's clock Button and popup white's clock button
If ActiveSheet.Shapes.Range(Array("Bevel 7")).Adjustments.Item(1) = 0 Then Exit Sub
ActiveSheet.Shapes.Range(Array("Bevel 7")).Adjustments.Item(1) = 0
ActiveSheet.Shapes.Range(Array("Bevel 6")).Adjustments.Item(1) = 0.15

'Stop the Ongoing time counter
On Error Resume Next
Application.OnTime NextTick, "UpdateClock", , False
Clocks = "B"
Call UpdateClock 'To start the B clock

End Sub
--------------------------------------------------------------------------------------------
Sub StartWhiteClock()
'AND STOP THE BLACK CLOCK!

'Depress white's clock Button and popup black's clock button
If ActiveSheet.Shapes.Range(Array("Bevel 6")).Adjustments.Item(1) = 0 Then Exit Sub
ActiveSheet.Shapes.Range(Array("Bevel 6")).Adjustments.Item(1) = 0
ActiveSheet.Shapes.Range(Array("Bevel 7")).Adjustments.Item(1) = 0.15

'Stop the Ongoing time counter
On Error Resume Next
Application.OnTime NextTick, "UpdateClock", , False
Clocks = "W"
Call UpdateClock 'To start the W clock

End Sub
---------------------------------------------------------------------------------------------
Sub UpdateClock()
'DIGITAL CLOCKS - there are 2: one for Blacks, one for Whites
If Clocks = "B" Then
'Set up the next event one second from now
NextTick = [BlackClock].Value + TimeValue("00:00:01")
[BlackClock].Value = [BlackClock].Value + TimeValue("00:00:01")
Else '"W" is implied
'Set up the next event one second from now
NextTick = [WhiteClock].Value + TimeValue("00:00:01")
[WhiteClock].Value = [WhiteClock].Value + TimeValue("00:00:01")
End If
Application.OnTime NextTick, "UpdateClock"
End Sub
-------------------------------------------------------------------------------------------

The Bold type statement just before End sub above is the one that presumably launches into a recursive. But it does not work.
Anytime I depress a button, the corresponding cell containing the time increments by 1 second, but that is all - no more action (with the recursive working it should update every second cumulatively until the other button is depressed).
My code structure was inspired by John Walkenbach's example ClockChart.xls; as a matter of fact it follows his exact logic
A picture of the actual Excel sheet also gives you an idea of what I am doing.

Could anyone tell me where I am going wrong in my code?

Notes: I use coding for range with [rangename] to replace the lenghty range("rangename")

Thank you in advance.

Corentint

 

[John_w]

I suspect your code doesn't work properly because:

1. Application.OnTime requires the actual date-time in the future to run, not the number of seconds.

2. With the UpdateClock procedure in a sheet module, Application.OnTime's Procedure argument must include the name of that module, i.e. Me.Name & ".UpdateClock" .

Try this code instead:

Sub UpdateClock()
    'DIGITAL CLOCKS - there are 2: one for Blacks, one for Whites
    If Clocks = "B" Then
        'Set up the next event one second from now
        NextTick = Now + TimeValue("00:00:01")
        [BlackClock].Value = [BlackClock].Value + TimeValue("00:00:01")
    Else '"W" is implied
        'Set up the next event one second from now
        NextTick = Now + TimeValue("00:00:01")
        [WhiteClock].Value = [WhiteClock].Value + TimeValue("00:00:01")
    End If
    Application.OnTime NextTick, Me.Name & ".UpdateClock"
End Sub

Change the other Application.OnTime calls in the same way.

 

[corentint]

Hey thank you very much. At first it did not work, with the message that it could not find the macro in { Application.OnTime NextTick, Me.Name & ".UpdateClock" }
Me.Name yields Board.UpdateClock; I looked up the tree and the sheet named Board is also Sheet1.
So, I updated the statement to : Application.OnTime NextTick, "Sheet1.UpdateClock" and it WORKED!
Thank a lot. This is yet another progress into my knowledge of VBA's intricacies. I appreciate it a lot.

Have a nice day.

Corentin

 

[John_w]

Thanks for the feedback. My mistake; it should be Me.CodeName, not Me.Name.