Is there a call stack level limit?

问题:

I have a couple of colleagues looking at some bad code in Excel VBA, wondering is there a limit to the number of levels in a call stack

回答1:

Unless the function is tail-recursive and VBA can handle that (which it can't), you'll run into a stack overflow.

As a simple test I hacked together the following snippet:

Dim count As Integer

Sub Rec()
    count = count + 1
    Cells(1, 1) = count
    Call Rec
End Sub

which tells us that the limit for this is 4007 iterations, at least in my version of Excel 2007 here.

回答2:

the short answer is yes, eventually you will get a stack overflow exception.

Not sure what the limit is though.

回答3:

I just ran this macro in Excel 2003, and got 4775 calls deep before I got error 28, "Out of stack space" :

Sub Macro1()
    recurse (0)
End Sub

Sub recurse(level As Long)
   ActiveCell.FormulaR1C1 = Str$(level)
   Call recurse(level + 1)
End Sub

回答4:

I ran Anders' code in Excel 2013 and the result was only 1180. Not sure if they have reduced the recursion limit in newer versions of Excel or if the problem is machine dependent or whatnot.

Edit: Also, changed: ActiveCell.FormulaR1C1 = Str$(level) to Range("A1").FormulaR1C1 = Str$(level)

And the # of calls dropped to 807. I had also had added Option Explicit.

回答5:

Stack memory consumption.

As i understand the stack if used for storage of parameters to procedures, and for local variables in the procedures.

Therefore will the consumption of stack space, be proportional to number of recursive calls.

So the needed size of the stack is something like: number of recursive calls * (size of calling parameters+ space for local variables)