Excel File Size & Memory Limitations? How best to proceed?

[BruceMutton]

Am developing a workbook to perform some engineering analysis and design calculations.

It is linked to a multisheet set of datatables.
Contains about 16 sheets, and these have about 28 embedded charts. A number of the sheets contain vba code pages to iterate to a solution on each calculate event. Being developed in Excel97 and WinNT with occaisional review in Excel 2000 and WinME. Ever since its being hovering around 1MB in size the file has been very temperamental, with spurious strange excel behaviour cropping up from time to time. Reverting to a recent backup and restarting Excel cures these problems. Other problems, such as "Out of Memory" occur, (especially when editing charts from user interface -editing by macro seems to be OK though) and files saved after this occurs seem to have permenant damage. Once again frequent backups save the day.

Even though both NT and ME seem to have plenty of physical/virtual memory unused, excel does not seem to be able to make use of it.

Is there something I can do to make more memory available to Excel?
Would I be better to develop a string of a larger number of (smaller) linked files, rather than few (larger) files?

 

[wowski]

I have experienced the same sort of problem when developing an Excel spreadsheet with a userform with a lot of input boxes. I tried unloading add-ins and all sorts but nothing seemed to work. Curiously I found that once I seemed to reach the memory limit, if I closed down excel, then loaded the worksheet with the macros disabled I could then save the worksheet with the changes made and then reload with macros enabled. I did this a number of times and it seemed to be the only way of adding extra textboxes etc. This is not the ideal situation and if anyone else knows how to increase the memory Excel uses I would also be grateful.

 

[ivymike]

I've run into a similar problem in the past. Check out this article and see if it helps:

http://support.microsoft.com/support/kb/articles/Q168/6/50.ASP

 

[ivymike]

another article that may help you:

http://support.microsoft.com/support/kb/articles/Q167/0/79.ASP

Am I the only one who finds it frustrating that Excel limits you to 256 columns in a worksheet?

 

[CTruax]

If the two KB articles don't resolve your problem

http://support.microsoft.com/support/kb/articles/Q168/6/50.ASP

http://support.microsoft.com/support/kb/articles/Q167/0/79.ASP

then I would suggest that you have a far-heap corrupt. This can occur when your app requests more stack space than memory has allocated as available.
Program a Hook at the stack-request and insert a pause-message that displays current memory states. This may isolate your problem to a specific excel object which can be modified to eliminate the problem.
If this is above your skill level, eMail me the sheet zipped and I'll check it for you.
StackCheckE-tips770-12656@builderspost.com

 

[BruceMutton]

Thank you all.
The MS KB articles referenced, and a few others, do not, I think, exactly apply to my problem, but are fairly close and point out a few pitfalls to be avoided.

More feedback welcome.

Ctruax's approach seems promising. I often have files which by deduction have a faulty component that is hard to pin point...

I will seek his or her help!

 

[narenr]

Try splitting the spreadsheet into several workbooks and link them at runtime and close after each use, I mean close the particular workbook after a particular task is performed. Work like dll's, that should reduce the runtime memory problems.


Narendranath R
narenr@narendranath.itgo.com

http://www.narendranath.itgo.com

Pipeline engineering is made easy with state of the art computer software, visit

http://www.narendranath.itgo.com.