When a function calls another function, Excel stores the location in
the caller function at which execution should resume when the called
function terminates. For example,
Sub Caller()
' do something
Call CalledFunction
'<<< COMPILER STORES THIS LOCATION
' do something else
End Sub
Sub CalledFunction()
' do something
End Sub
Here, when CalledFunction finishes, execution returns to the location
marked wth <<<. These return addresses are stored in a data structure
called a "stack". (Think of it like a stack of plates. New plates are
added at the top of the stack, and plates are removed from the top of
the stack.)
Badly written code can cause loops when a function calls another that
calls back to the first or when a function calls itself. For example,
Sub Caller()
' do something
Call CalledFunction
'<<< COMPILER STORES THIS LOCATION
' do something else.
End Sub
Sub CalledFunction()
' do something
Call Caller
' <<< COMPILER STORES THIS LOCATION
' do something else
End Sub
Here, Caller calls CalledFunction, which calls Caller, which calls
CalledFunction, which calls Caller, and so on forever. The compiler
must store all these return addresses in the stack. At some point, the
stack gets full (it runs out of memory), and VBA blows up with an "out
of stack space" error.
The most common cause of an "out of stack space" error is with the
Worksheet_Change event. If the code within the Worksheet_Change event
changes some cell, that change calls Worksheet_Change, which changes a
cell, which calls Worksheet_Change, which changes a cell, which calls
Worksheet_Change, and so on until there is no longer any room in the
stack to store the return addresses. The following is a very
simplistic example of this:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Target.Value + 1
End Sub
Here, _Change changes Target, which causes _Change to run, which
changes Target, which causes _Change to run, which changes Target,
which causes _Change to run, and so on until VBA blows up with an "out
of stack space" error.
To prevent this from happening, you can set the EnableEvents property
to False, which prevents Excel from running events. For example,
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Target.Value + 1
Application.EnableEvents = True
End Sub
With EnableEvents = False, changing Target won't cause the Change
event to run, so Target isn't changed again, and Change isn't called
again (and so on).
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
