Search code examples
excelvbarecursionstack-overflow

Out of Stack Space Issue - Can I Repeat a Sub Without Recursing It?

I'm working on a fun project in Excel making a game. It works perfectly except that after about a minute of run time it errors because it's out of stack space. I realized this was because technically I'm using recursion in the stepEvent function, which looks like this:

Private Sub stepEvent()
    ' All the game code
    start = Timer
    Do While Timer < start + 0.15
        DoEvents
    Loop
    stepEvent
End Sub

None of the instances of stepEvent ever resolve until either the player loses, or it runs out of stack space and errors. My question is if there's a way to either resolve the recursed subs, but somehow retain control, or if there's an alternate method to repeat that sub without using recursion.

Solution

  • Try looping

    Sub asDoWhileLoop()

   Do While True
       Call stepEvent
   Loop

End Sub

Sub stepEvent()

    'Do game stuff and wait at end

End Sub

    But remember to clean up at the end of your stepEvent(), i.e. set your objects to NOTHING, close recordsets, whatever you are playing with. You can quit using END instead of EXIT SUB. But it's all up to you to ensure it terminates.