Out of memory when saving file

[GOGGS75]

Hi

I hoping someone can suggest any reasons for a problem I'm having or let me know if they have had the same problem.

A macro-driven spreadsheet I built has started crashing when saving it (cntrl-s). It comes up with an error message 'system error &h8000ffff' followed by an 'out of memory' message.

The spreadsheet has 7 userforms with quite a bit of code but I have built some larger than this before with no problem.

I have tried various approaches to sort this but the problem keeps coming back. The approaches have been as follows:

1.) Replace each worksheet (15 of them) with freash ones in-case something was corrupt.
2.) Make sure all objects are set to nothing at the end of the macro.
3.) Save some of the userforms as separate 'frm' files and load them when the macro runs.
4.) Convert the file to XML format then back to XLS to clear the macro completely, then rebuild it using frm files
5.) Try Ron Ron De Bruin's Code cleaner add-in

The same problem is occuring in Excel 2003 and when converted to an Excel 2007.xlsm file.

An error message at one stage was stating that the file could not be saved because somthing was wrong with a VBA or External component.

I am puzzled as to why the problem happens when saving the file and only sometimes happens.

Thanks for any advice.

Goggs75

[snb]

Is there any Event code in the Workbook_beforeClose event ?
Does the workbook contain any connections or links ?

[GOGGS75]

Hi

There is no event code for any workbook events and no links to separate files.

Cheers

Goggs

[snb]

What do you mean by 'A macro-driven spreadsheet' ?

[Paul_Hossler]

What version of Excel?

In 2007/2010 the .xlsx is really a .zip file. I've been able to narrow down errors like that by renaming it to .zip and opening it with a zip reader to see if there's anything unexpected.

Found things like a huge styles folder that required a lot of cleanup

Paul

[GOGGS75]

Hi

My term 'macro-driven spreadsheet' was just trying to describe a spreadsheet with some userforms built in to enable automation of various processes.

I started to develop the spreadsheet at work on Excel 2003 but have completed some of it at home on Excel 2007 in compatibility mode. When transferring between the two PC's I've used '.frm' files to transfer the full userforms and never transferred a full '.xls file'.

There have been various signals it is something to do with the macro components causing the problem but something is puzzling. I tried converting the file to XML to get completely rid of the vb project and then converted this back to XLS format. I then recreated 4 of the 7 userforms from scratch without adding any code to them. File save was working no problem but then stopped with the 'out of memory' message. Only 4 userforms with no code behing them?

[snb]

Did you hide/unload the userforms before closing the file ?

In my opnion a workbook seldom needs more than 1 userform.
Why do you use so many ?

[GOGGS75]

I unloaded all userforms before saving the file. I am needing to use more than one userform because of submenus and forms for inputting data existing below the main menu.

[snb]

You can achieve all that using frames and/or multipage & resizing the userform at runtime.

The problem could also be the result of a recently installed addin (Skype, Itumnes, etc.).
Try to unload all addins and test again.

[GOGGS75]

Surprisingly the problem has not happened at all today. I will check the memory using the task manager if it crashes again to see if this indicates what is using the memory up.

Thanks for your suggestions