VBA just dies - EXCEL 97

[dranon]

I have a button on the first of 30 worksheets which does a lot of work
behind the scenes: deleting worksheets, populating cells, creating new
worksheets.

Most of the time, but not frequently enough, it runs flawlessly.

But every once in a while it will start the process, delete all the
worksheets it is supposed to delete, create a lot of the sheets it is
supposed to create and then, near the end of the process, before it
has completed 100% of the sheets it is supposed to create, it just
stops dead in its tracks. In order to recover I have to go into VBA
(alt-11) and enter a "application.screenupdating = true" command into
the immediate window.

However, while the above gives me access to my excel workbooks again,
the button will now continually fail until I close all open workbooks,
completely exit excel and fire everything back up again.

Since I routinely have 10 or more workbooks open at once, I'd really
like to figure out a way for me to be able to run the routines
associated with the command button again without having to restart
Excel.

Yes, I have tried just shutting down the offending workbook and
calling it up again. No go. In fact, I've closed down all workbooks
and called up the offending workbook and it still fails. Only
restarting Excel seems to work.

Any idea how to go about testing this?

Thanks.

 

[dranon]

I have a button on the first of 30 worksheets which does a lot of work
behind the scenes: deleting worksheets, populating cells, creating new
worksheets.

Most of the time, but not frequently enough, it runs flawlessly.

But every once in a while it will start the process, delete all the
worksheets it is supposed to delete, create a lot of the sheets it is
supposed to create and then, near the end of the process, before it
has completed 100% of the sheets it is supposed to create, it just
stops dead in its tracks. In order to recover I have to go into VBA
(alt-11) and enter a "application.screenupdating = true" command into
the immediate window.

However, while the above gives me access to my excel workbooks again,
the button will now continually fail until I close all open workbooks,
completely exit excel and fire everything back up again.

When I say it fails, it fails the same way as the first failure: it
deletes and creates most of the worksheets and then it just dies. I
didn't mean to say that the button doesn't do anything. It does. And
all my message boxes run properly, too.

 

[Billy Liddel]

There is a limit of 64KB in a single module. Copy the procedures into Notepad
and save the file. If Notepad file is this big then in the Visual basic
Editor, declare each Sub as Public, e.g. Public Sub MyTest() and copy some
procedures into a new module.

Modules exceeding the 64KB limit tend to be unstable.

Maybe someone more proficient can give better suggestions.

HTH Peter A

 

[dranon]

Thanks for the reply, Billy.

The code, although it does a lot of work, is pretty tightly written
and has a very small footprint.

Anybody else have any ideas?

 

[dranon]

Anybody?

Thanks for the reply, Billy.

The code, although it does a lot of work, is pretty tightly written
and has a very small footprint.

Anybody else have any ideas?

 

[Ed from AZ]

Anybody?

I'm definitely not an expert, but I might qualify as "anybody"!

Have you checked to see if the code always fails at one certain
command, or within one specific routine?
Have you allowed Excel and your computer enough time to process
everything that's happening?
Do you have another process that's jumping in and hogging memory?
Have you tried things like clearing the Undo list and maybe throwing
in the screen updating, since that seems to help?
Maybe a few Do Events or Sleep to let the computer catch up to Excel?

I'm also adding the Excel.Programming group to this - you'd probably
get better answers there.

Ed

 

[Ed from AZ]

Prob'ly ought to add the rest of the original post to this:

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/b06f5592c181d1e1#