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
+2
A:
the short answer is yes, eventually you will get a stack overflow exception.
Not sure what the limit is though.
Sam Holder
2009-12-08 10:28:05
based on the other answers it seems that the limit is probably dependent on a few factors, and so you shouldn't rely on any particular number. As long as you are not doing massive recursion, you should be ok and not hit the limit.
Sam Holder
2009-12-08 10:40:13
The limit will be related to memory available to Excel's in process runtime (VBA) which is further limited by the amount of user mode space allocated by the OS. In practise I don't think you can trap this error (due to AV) but I might be wrong. Just don't write crappy recursive functions.
Anonymous Type
2010-10-12 21:40:48
+2
A:
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
Anders Lindahl
2009-12-08 10:35:01
+2
A:
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.
Joey
2009-12-08 10:37:44