CODEWITHSUNDEEP
excelvbacountdown
Passer-by
Passer-by

Reputation: 71

Problem with building count down timer in Excel using VBA

I was trying to build a count down timer using VBA, and the result can be dynamically output to an Excel cell. I let procedure abc and def recursively call each other (did not set a stop point just for testing), and it worked. However, later with this exact same code I ran again, it failed, and error message was:

Code execution has been interrupted.

Just can't figure out why, I didn't change anything, how could it work and then fail?

I tried On Error Resume Next and Application.DisplayAlert = False, both don't stop the error message popping up and the interruption of the code execution. And if I step through the procedures, it seems fine...

Also I wish to add a dynamic text like "start in how many seconds" like in the comment in another cell. Can it be realized in this way?

Thank you!

Sub abc()

    [a1] = [a1] - 1
    ' [a2] = "Start in " & [a1] & " seconds."
    Call def

End Sub


Sub def()

    Application.Wait (Now + TimeValue("0:00:01"))

    Call abc

End Sub

Upvotes: 0

Views: 382

Answers (3)

Andy G
Andy G

Reputation: 19367

Rather than trying to do this recursively, with concerns about the call stack, I would use Application.OnTime.

Sub Button1_Click()
    Call MyTimer

End Sub

Sub MyTimer()
    [a1] = [a1] - 1

    Application.OnTime Now + TimeValue("00:00:01"), "MyTimer"
End Sub

I suppose this is still 'recursive' in a fashion, but the procedure exits each time. Only after 1 second has elapsed does it execute the procedure again.

But, either way, you should include some means of stopping the process. For example,

Sub MyTimer()
    [a1] = [a1] - 1

    If [a1] > 0 Then
        Application.OnTime Now + TimeValue("00:00:01"), "MyTimer"
    End If
End Sub

Upvotes: 3

Daniel
Daniel

Reputation: 954

Why not create a function btw? Does it work for you?

Function wait(seconds As Long)

    Dim originalStatusBar As String, i As Long
    originalStatusBar = Application.StatusBar

    For i = seconds To 0 Step -1
        Application.wait (Now + TimeValue("0:00:01"))
        Application.StatusBar = "Start in " & i & " seconds"
    Next

    Application.StatusBar = originalStatusBar

End Function

Then in your sub you just call it like so:

wait 5 'waits 5 seconds and updates status bar with remaining time

or

wait [a1]-1

Upvotes: 0

ellebaek
ellebaek

Reputation: 11

Your entire code is working fine for me (also including the [a2] part). I'm on Windows 7 with Excel 2013.

I suggest you include a stopping condition to abc() like

If [a1] > 0 Then
   Call def
End If

Please provide some more information.

Upvotes: 0

Related Questions