Problem with building count down timer in Excel using VBA

Question

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

I would go with Application.OnTime rather than trying to do something recursively (with concerns about the call stack). — Andy G
– Andy G, Commented Mar 20, 2019 at 14:23
Like Andy mentions, using Application.Wait is holding you back since it locks everything down while it runs. This site has an interesting implementation that includes DoEvents instead — Marcucciboy2
– Marcucciboy2, Commented Mar 20, 2019 at 14:29

Andy G · Accepted Answer · 2019-03-20 14:29:19Z

3

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

answered Mar 20, 2019 at 14:29

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Passer-by Over a year ago

I realized that the reason why my code first worked and then failed must have something to do with "call stack" you mentioned, although I'm not exactly sure what does it mean, but I feel it's like something is taking up the memory that kind of stuff, because when I try to run my other program, it also return the error message of interruption! So what exactly did my countdown program do in the background so that caused this problem?

Andy G Over a year ago

Roughly, each time a procedure is called some space/ data structure is allocated for it to store its state; think about this as storing local variables. Because the procedure calls itself, another such block is created, and another. Until the whole sequence exits (unwinds) this storage accumulates, potentially leading to a stack overflow. OnTime avoids this because the procedure exits each time; it just calls it again after a second.

Andy G Over a year ago

"Code execution has been interrupted.", though, suggests that the code was interrupted by pressing Stop or Ctrl-Break, rather than indicating a problem with the stack. (You would have to interrupt it because you don't have an escape clause.)

Passer-by Over a year ago

Thank you so much! So did you mean that in the MyTimer sub, as soon as Application.OnTime is executed, the sub ends because it takes far less than a second for the program to reach and execute the End Sub line, and computer only needs to allocate space for one program actually. So in other word, suppose the time interval assigned to OnTime is smaller than it takes to run the rest of the code until End Sub is reaches, then it will recursively call the other Sub and cause call stack problem again? If I understood correctly?

Passer-by Over a year ago

I also tried within one Sub to add Application.wait inside a Do While Loop, but also return the execution was interrupted error message, not quite understand what is it now in this case where there’s only one Sub, so shouldn’t be call stack, just try to “slow down” the loop.

|

ellebaek · Accepted Answer · 2019-03-20 14:19:26Z

0

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.

answered Mar 20, 2019 at 14:19

ellebaek

113 bronze badges

Comments

Daniel · Accepted Answer · 2019-03-20 14:27:59Z

0

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

edited Mar 20, 2019 at 14:27

answered Mar 20, 2019 at 14:22

Daniel

9546 silver badges12 bronze badges

Collectives™ on Stack Overflow

Problem with building count down timer in Excel using VBA

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related