I have written a macro that uses a list box and is populated and items
removed depending on certain conditions in excel.

I was then in the internet and when I went to the TV guide website it gave
me a Stack Overflow error on the page. Can that be caused by something going
on in Excel?

Thank you,

Steven

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)







On Sun, 4 Jan 2009 19:56:13 -0800, Steven
wrote:

I have written a macro that uses a list box and is populated and items
removed depending on certain conditions in excel.

I was then in the internet and when I went to the TV guide website it gave
me a Stack Overflow error on the page. Can that be caused by something going
on in Excel?

Thank you,

Steven