Out of Stack Space

[Paul Sardella]

Good morning.

Using Excel97 SR2 on Windows 98SE.

Can anyone point me to a resource that will help me deal with "Out of Stack
Space" errors? I get them occasionally (once every couple of days). Different
workbooks. Various subroutines. No UserForms oe Dialog Boxes involved. I
recently have included a lot of OnTime subs (not related to stack errors). I'd
like to eliminate a potential source of problems (chain effect).

I understand the purpose of a stack. Push, pop. Problem with recursive routines
and incorrect loops. Problem with too many subs calling other subs (nesting).
The use of too many Variants. Passing too many variables by value to other subs.

Is there a way to examine what's being left on "the stack"? Is there a way to
increase the size of "the stack" (I'm assuming that it is fixed in size)?

Is there documentation that shows how Excel handles its stack?

TIA
Paul

[Chip Pearson]

Paul,

You can view the call stack when you are in break or debug mode. Go to the View
menu, and choose "Call Stack". It is not possible to modify or resize the stack,
nor can it be examined programmatically.

Do you have any event procedures in either the offending workbooks or in an
add-in? You can get "out of stack" errors when an event procedure causes itself
to be called. For example, if the worksheet_change event makes a change to a
cell, that will call worksheet_change which changes a cell, which calls
worksheet_change, and on and on.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3BFA6ECB...@cheerful.com...

[Dana DeLouis]

I wanted to ask the same question for Excel XP.

Some suggestions for "Out of stack space (Error 28)" can be found in the
following KB article.

OFF: An Explanation of Trappable Errors in Visual Basic for Apps
http://support.microsoft.com/support/kb/articles/Q142/1/38.ASP

I was playing around with the following little macro...
In Excel XP, I got the following little program to loop 6,578 times before
running out of stack space.
If I remember correctly, this would only loop about 2,000 times in previous
versions.
Maybe someone can confirm that Excel XP offers a "bigger" stack???
Again, I do not remember exactly anymore.

Option Explicit
Dim X As Long ' Public Variable

Sub StackCheck()
Debug.Print X
X = X + 1
StackCheck
End Sub

Here is a short copy of the article for Error 28.
===========
Out of stack space (Error 28)
Possible causes for this error are:

Too many active Function or Sub calls. Check that general recursive
procedures are not nested too deeply and that they terminate properly.

Local variables require more local variable space than is available. Try
declaring some variables at the module level instead. You can also declare
all variables in the procedure static by preceding the Property, Sub, or
Function keyword with Static. Or, you can use the Static statement to
declare individual static variables within procedures.

Fixed-length strings use more stack space than variable-length strings. Try
redefining some of your fixed-length strings as variable-length strings.

Too many nested DoEvents statements.

Use the Calls dialog box to view which procedures are active (on the stack).
To display the Calls dialog box, select the button to the right of the
Procedures box in the Debug window.
===========

I am a little confused about the statement for Fixed-Length strings. I did
not think that this could affect Stack space. But...that's what it says.
Hmmm.

--
Dana DeLouis Windows Me & Office XP

"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3BFA6ECB...@cheerful.com...

[Paul Vespo]

I was using Excel 97 and had a huge workbook. I was consistently running
out of stack space. In speaking with Microsoft there is a limitation Excel
has. It is a type of cache where formulas reside. Excel 2000 and before
has a 64 Meg limit. Excel XP has been increased to 128 Meg. This has made
it possible for me to do my work now. The same work that was failing under
XL97 and 2000.

Somethings that also help are:
Q) The Index/Match function somehow takes less memory yet performs the same
function. Can you explain if this is correct and if so how the two functions
utilize the memory along with their differences?
A) It is true that INDEX(MATCH()) is less memory intensive than VLOOKUP(). I
couldn't tell you the exact reason for this but we've seen the difference
here numerous times.It also does the same as VLOOKUP, just with a different
syntax. The sample I gave you would produce the exact same results as the
VLOOKUP function.
Also, if you are performing the same function within a single formula
multiple time then memory is being used for each of those instances within
that formula. You can cut back on memory by breaking your formula out.
I'll try and provide and example to explain:

My original formula
A1
=IF(ISNA(VLOOKUP($A1,'FROM_IFSGL(pymts_daily_activity'!$27:$6012,'FROM_IFSGL
(pymts_daily_activity'!Q$14,FALSE)),IF(ISNA(VLOOKUP(A1,PRIOR_CS!$27:$6026,PR
IOR_CS!P$14,FALSE)),0,VLOOKUP(A1,PRIOR_CS!$27:$6026,PRIOR_CS!P$14,FALSE)),VL
OOKUP($A1,'FROM_IFSGL(pymts_daily_activity'!$27:$6012,'FROM_IFSGL(pymts_dail
y_activity'!Q$14,FALSE))
The above has 6 calculations each taking up its owm memory. This could be
broken out as follows to free up memory. Below only has 4 calculations each
takin up their own memory space.

A1=VLOOKUP($A1,'FROM_IFSGL(pymts_daily_activity'!$27:$6012,'FROM_IFSGL(pymts
_daily_activity'!Q$14,FALSE)
B1=VLOOKUP(A1,PRIOR_CS!$27:$6026,PRIOR_CS!P$14,FALSE)
C1=IF(ISNA(A1),IF(ISNA(B1),0,B1),A1)

Now this takes some carefuly analysis of formulas but is doable... I woukld
recommend purchasing XP first before breaking out the formulas. You can
download a 30day eval copy and test for free from Microsofts web site.

Hope this Helps,

Paul

"Paul Sardella" <PSar...@cheerful.com> wrote in message
news:3BFA6ECB...@cheerful.com...