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

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
>
> .
>