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

 

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