Controlling execution of a Sub Procedure

[Don McC]

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]

There's a few reasons for getting stack overflow, it could be that you
procedure is changing the worksheet which triggers a shee
change/calculation which starts he procedure anew. It could also be tha
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: 'se
Recursion'.

I have a spreadsheet that itself has a recursive (iteration) loop.
Once the

 

[Don McC]

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.