I have a spreadsheet that itself has a recursive (iteration) loop. Once the
sheet converges, I have to look at the results and depending on what they
are, either call a lengthy Sub procedure or bypass it. I cannot use Function
procedures because the lengthy calculation I must perform is itself
iterative. Thus I can't break up the calculation into a number of small
Function procedures. I am now using the worksheet_calculate() event handler,
but evidently the definition of "recalculate the spreadsheet" includes any
cell that is manipulated. Thus what I am running into is the
Worksheet_calculate() sub procedure is being called over and over until the
stack overflows, EVEN though the TEST spreadsheet I am using has only one
cell of data that I change and one calculation. Everything else is static.
Anyone have ANY suggestions around this problem? Any way to call a sub
procedure from the spreadsheet itself? I am using Excel 2003 with all the
patches and updates.

Thanks

There's a few reasons for getting stack overflow, it could be that your
procedure is changing the worksheet which triggers a sheet
change/calculation which starts he procedure anew. It could also be that
your recursive procedure does not have adequate exit strategies.

Is your recursive procedure truly recursive (that is, it calls itself,
or is it just iterative as in a loop of some sort?

Give us some meat to work on, post some code etc.

ps. There's no reason a vba function can't call itself, recursively,
iteratively or any other way, whether it passes arguments or not.

I looked up 'recursion' in the dictionary and all I got was: 'see
Recursion'.


Don McC;703105 Wrote:

I have a spreadsheet that itself has a recursive (iteration) loop.
Once the
sheet converges, I have to look at the results and depending on what
they
are, either call a lengthy Sub procedure or bypass it. I cannot use
Function
procedures because the lengthy calculation I must perform is itself
iterative. Thus I can't break up the calculation into a number of
small
Function procedures. I am now using the worksheet_calculate() event
handler,
but evidently the definition of "recalculate the spreadsheet" includes
any
cell that is manipulated. Thus what I am running into is the
Worksheet_calculate() sub procedure is being called over and over until
the
stack overflows, EVEN though the TEST spreadsheet I am using has only
one
cell of data that I change and one calculation. Everything else is
static.
Anyone have ANY suggestions around this problem? Any way to call a
sub
procedure from the spreadsheet itself? I am using Excel 2003 with all
the
patches and updates.

Thanks


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196715

http://www.thecodecage.com/forumz

The problem was in the posting back of results to the same page. Once I
wrote back the results of the Worksheet_calculate() sub procedure to another
page it worked fine. You wouldn't have wanted me to post the code. I
basically had to duplicate one whole spreadsheet within code, so the code was
about 7 standard pages worth....I sure wish excel had a way to control
execution of parts (ranges) of cells better. Worksheet_change and
Worksheet_calculate don't always work very elegantly.

"p45cal" wrote:


There's a few reasons for getting stack overflow, it could be that your
procedure is changing the worksheet which triggers a sheet
change/calculation which starts he procedure anew. It could also be that
your recursive procedure does not have adequate exit strategies.

Is your recursive procedure truly recursive (that is, it calls itself,
or is it just iterative as in a loop of some sort?

Give us some meat to work on, post some code etc.

ps. There's no reason a vba function can't call itself, recursively,
iteratively or any other way, whether it passes arguments or not.

I looked up 'recursion' in the dictionary and all I got was: 'see
Recursion'.



Don McC;703105 Wrote:

I have a spreadsheet that itself has a recursive (iteration) loop.
Once the
sheet converges, I have to look at the results and depending on what
they
are, either call a lengthy Sub procedure or bypass it. I cannot use
Function
procedures because the lengthy calculation I must perform is itself
iterative. Thus I can't break up the calculation into a number of
small
Function procedures. I am now using the worksheet_calculate() event
handler,
but evidently the definition of "recalculate the spreadsheet" includes
any
cell that is manipulated. Thus what I am running into is the
Worksheet_calculate() sub procedure is being called over and over until
the
stack overflows, EVEN though the TEST spreadsheet I am using has only
one
cell of data that I change and one calculation. Everything else is
static.
Anyone have ANY suggestions around this problem? Any way to call a
sub
procedure from the spreadsheet itself? I am using Excel 2003 with all
the
patches and updates.

Thanks


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196715

http://www.thecodecage.com/forumz

.