Limits of Excel

[black_sun]

Hi,
how can I control the limit of Excel?
I mean... Excel has the limit about sheets dipending from memory of
the pc.
Is there a way to stop it before it crashes??
I have a macro which generate lots of sheets with charts but after "n"
sheets it crashes.... I would like to know how I can intercept this
behaviour?

Thanks in advance

Bye Denis

 

[myemail.an]

Which version are you using? Excel 2007 sucks: is is much slower and
has many more bugs than 2003. Some VBA macros crash for no reason in
2007 but work fine with 2003.

Other tips I can think of:
- make sure you remove unnecessary rows and columns from your sheets
and save the file after doing so (this once solved a similar problem I
had)
- make sure you clear the clipoboard (application.cutcopymode=false)

Does it always crash at the same point in the code?

 

[Bob Phillips]

What do you mean by ... after n sheets it crashes?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[black_sun]

Hi,
that it crashes always with a different number of sheets generated.
One time with 30 sheets... the other one with 24... there's no sure
number...
I read it depends from memory pc.

Bye Denis


Bob Phillips ha scritto:

 

[black_sun]

Hi,

Which version are you using? Excel 2007 sucks: is is much slower and

Excel 2003

Other tips I can think of:
- make sure you remove unnecessary rows and columns from your sheets
and save the file after doing so (this once solved a similar problem I
had)

Every sheet has 4 charts and nothing else... I can't delete
anything

- make sure you clear the clipoboard (application.cutcopymode=false)

I could be this but at the maximum I copy in clipboard 52 cells... I
don't think it could be...

Does it always crash at the same point in the code?

It crashes during creation of the charts... I can't see where it
crahes as Excel close itself without warning.

Bye Denis

 

[Mike Fogleman]

Save the workbook every so many sheets to clear the memory.
something like:
If Sheets.Count Mod 20 = 0 then ThisWorkbook.save
would save after every 20 sheets.
Untested
Mike F

 

[myemail.an]

Every sheet has 4 charts and nothing else... I can't delete
anything

Check the last used cell (either via VBA or clicking CTRL+END). It
often happens that, if you copy entire sheets or columns, Excel
actually stores 65k rows, even if most of them are blank.

It crashes during creation of the charts... I can't see where it
crahes as Excel close itself without warning.

And if you debug the code setting breakpoints, can you identify where
the problem is?

If it closes without warning, you can't use the statusbar to describe
what portion of the code is running. You could, however, set up a
worksheet, name it "Debug", and write some key metrics to its cells,
e.g. write what step of the macro has just ran, how much memory is
left (there is a way of doing this in 2003 but not in 2007; if you
google I'm sure you'll find something). If you make sure the macro
saves the file after you write to these cells, it might help you
identify the problem.

Can;t think of much else, sorry. I take it you set
application.screenupdating=false and closed all other programs before
running the macro.

 

[Guest]

If this is the same error that occurs when copying lots of worksheets without
closing the workbook, the only thing that has solved the problem for me is a
save and full close of the workbook. Lucky for me, I ran into the problem in
a situation where two workbooks would always be open so I used OnTime to
save/close/reopen first one then the other.
If you haven't already seen this, here's the link to the MS report about the
sheet copy bug: http://support.microsoft.com/kb/210684