×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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?

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

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.

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

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

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

(OP)
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!

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

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 www.narendranath.itgo.com.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources