Excel Crashes Repeatedly

I have a Monte Carlo analysis that runs on my Excel 97 and as desired
will run for as many hours as I allow it. It's a bunch of spreadsheet
code that's tied together by a fairly simple VBA macro to plug
semi-random numbers into the spreadsheet and monitor the results.

If Excel is the only program active, then all is well and it will run to
conclusion in 10 hours or so. However when something else is running
such as a browser, e-mail, screen saver, etc., it will regularly lock up
Excel and XP reports it as "program not responding". Closing Excel is
the only answer.

My question is whether I've overlooked something in VBA that one needs
to do to periodically relinquish control of the system or something? I
wouldn't think such a thing would be necessary, but I can't imagine what
else is going on.

Thanks...

Bill

Bill

It really depends on your code. If it runs for that length of time you
should make sure it releases all variables on each iteration and clear
clipboard, etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Bill Martin -- (Remove NOSPAM from address)" <wylie@earthNOSPAMlink.net>
wrote in message news:ubZLREUZFHA.3852@TK2MSFTNGP10.phx.gbl...
>I have a Monte Carlo analysis that runs on my Excel 97 and as desired will
>run for as many hours as I allow it. It's a bunch of spreadsheet code
>that's tied together by a fairly simple VBA macro to plug semi-random
>numbers into the spreadsheet and monitor the results.
>
> If Excel is the only program active, then all is well and it will run to
> conclusion in 10 hours or so. However when something else is running such
> as a browser, e-mail, screen saver, etc., it will regularly lock up Excel
> and XP reports it as "program not responding". Closing Excel is the only
> answer.
>
> My question is whether I've overlooked something in VBA that one needs to
> do to periodically relinquish control of the system or something? I
> wouldn't think such a thing would be necessary, but I can't imagine what
> else is going on.
>
> Thanks...
>
> Bill

It's likely a buffer overflow error caused by your clipboard not being cleared. Something running for that long can easily cause this type of issue. I imagine the crashes only occur if it's been running for an extended period. If not, please update and include any error messages you're receiving.

Nick Hodge wrote:
> Bill
>
> It really depends on your code. If it runs for that length of time you
> should make sure it releases all variables on each iteration and clear
> clipboard, etc
>

Hmmm.... The Monte Carlo macro itself is dead simple and doesn't really
use any variables other than an integer index for the outer For/Next
loop. Other than that it just sets a few cells equal to the contents of
some others plus/minus a random delta. And then looks at the single
resultant cell to decide whether to keep these new values or not.

Buried within the spreadsheet code being used though are custom VBA
evaluation functions that set up fairly large data arrays and operates
on them. These functions are probably called 20 or 30 times for each
pass of the low content Monte Carlo routine itself. I would think that
these functions release their memory each time they complete execution
though.

I'll go through all the code again though with an eye to that sort of
problem. And thanks for the suggestion...

Somehow the act of XP moving it's focus among the various active
programs seems to trip up Excel. Like it's a memory management issue
perhaps. Maybe I'll send a copy of the sheet to a friend who has the
current version of Excel to see if it runs any more robustly on his machine.

Bill

guilbj2 wrote:
> It's likely a buffer overflow error caused by your clipboard not being
> cleared. Something running for that long can easily cause this type of
> issue. I imagine the crashes only occur if it's been running for an
> extended period. If not, please update and include any error messages
> you're receiving.
>
>

There are no error messages. Excel just goes silent and sits quietly
frozen doing nothing. If I invoke the XP task manager it simply says
that Excel is not responding. The only way out of the situation is to
kill Excel and restart it from scratch.

I do not use the clip board for anything in this program. However, is
there some VBA command I could use to flush the clip board periodically
to verify this is not a problem?

The system will never crash if Excel is the only thing running so I
wouldn't think it's some simple buffer overflow caused by Excel alone.
On the other hand there may be some problem with Excel storing away it's
buffers as XP switches the task about?

If I run other simple tasks, Excel may freeze up immediately or in two
hours or never. I do not see this Excel behavior though while I have
other spreadsheets active -- which of course aren't doing anything but
sitting passively anyhow. I don't have any other spreadsheets that take
extended time to run. Perhaps I should create another one that does
nothing but burn execution cycles and see how stable that is....

Bill