View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Don McC Don McC is offline
external usenet poster
 
Posts: 6
Default Controlling execution of a Sub Procedure

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

.