Or Maybe I Can't Do This

[rowyn]

Hrmph. My report gets through about 10 iterations, then crashes with a stack-overflow error. Which appears to be caused by Excel VBA call stack filling up with my auto-generated macro calls. Googling about for "how to clear the call stack" has not made me optimistic about my odds of getting around this via VBA.  x.x

Comments

[level-head.livejournal.com]

It may be that your procedure is calling itself. If that's the case, you can have a parent procedure that calls the child one over and over, so the child one exits each time and clears its stack.

===|==============/ Keith DeHavelle

[rowyn]

It's actually getting called over and over by an event. The report kicks off by grabbing a bunch of data from an SQL add-on. Then it has to wait for that data to get refreshed (so I can't just have it call the next step directly.) Instead, there's an event that kicks off when the refresh is complete, which says "save this batch of data under a new filename, then get a new different batch". And when that finishes refreshing, it triggers the same event again.

[level-head.livejournal.com]

Is the first-called event exiting before it's triggered again? Perhaps, instead of taking off again, it can set a condition in a shared variable and exit, and that shared variable tells the parent procedure to call the child procedure again?

If it's still in the child procedure when it's triggered again, I can see why the system is eventually recursing at you. ];-)

===|==============/ Keith DeHavelle

[tuftears.livejournal.com]

I wonder if that's where it's being considered a loop? That is:

1. Data refresh event fires
2. Script saves data under new file name and initiates new batch
3. New batch is loaded, but this is still under the script since it is waiting for the return value of the command
4. Data refresh event fires (but inside the first script)
5. Now you have a fresh copy of the script running for the new filename

And so on until it runs out of stack.

If that were the case, what you'd want to do is (I'm not versed in Excel so I dunno) make it so the script explicitly isn't waiting for the 'get different batch' stuff to return, it just fires it off and leaves.

[terrycloth.livejournal.com]

Okay, so the problem is probably that the event call doesn't exit until the fetch is complete, which means that all the events are still on the stack until the last one finishes.

Could you move the 'get a new different batch' step into the outer loop, with some sort of delay so that it didn't try to fetch multiple batches concurrently? Or it could wait on a variable that you set in the event handler (checking the variable every X time period until it's true, then fetch a new batch).

[whitefangedwolf.livejournal.com]

It sounds like you need your code logic to look something like the following:

For Each batch_to_fetch In list_of_batches_to_fetch
      'Grab the batch data'
      Do
             'Check if data is refreshed
      Loop While {Data is not refreshed}
      'Save batch under a new filename
Next