"Microsft Excel: Out of Memory!" errors but plenty available (both physical & virtual)
David McRitchie
Do you have a lot of graphics/shapes/buttons these use Graphical
resources.
Look at your Tasks and see how much memory Excel itself is using.
Someone calls himself Gozo pointed
out that Excel can only go up to about 64MB, and
this can happen when you have like 8000 x 256 cells dependent
on the value in one cell. i.e. each cell is valued at the 1 + value of
the previous cell.
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Greg Cocks <gco...@aesibi.com> wrote in message
news:396D2048...@aesibi.com...
> Hi,
>
> I am getting "Microsoft Excel: Out of Memory!" errors but plenty is
> available (both physical & virtual) according to the Task Manager. I am
> attempting to open an Excel 97 file that I had open recently with no
> hassles. Details are:
>
> * Excel file is 180Mb in total and is stored on LAN
> * software is Excel 97 'upgraded' to SR2b service pack
> * local drive containing <pagefile.sys> has 480Mb free space
> * machine is SGI Visual Workstation 500MHz Xeon with 460Mb of actual
> RAM available (other 40Mb is used by video) running NT 4.0a with
> SP6a
>
> There is about 80Mb used by system processes, and then watching the file
> open with Task Manager in the foreground I can see the memory used creep
> up (not surprisingly) - it gets to 312Mb or so used and "flat lines" and
> then gives me the (annoying) "Microsoft Excel: Out of Memory!" dialogue
> box. In the past it has gone beyond 312Mb in Excel and other
> applications. Weird...
>
> I tried (with no success) various combinations of:
>
> * restarting in case memory was fragmented
> * trying it on another NT machine (866MHz Micron with 256Mb RAM and 2
> Gb free HD space on drive with <pagefile.sys>)
> * moving Excel file to a local drive
>
> Like I said, why now? I assume Excel has no top end of memory it can
> utilise - but if it does, why does it show up now?
>
> Looking forward to any and all input... emailing any replies (to
> <gco...@aesibi.com>) as well as posting would be excellent!
>
>
> Thanks in advance:
> GREG...
>
Charles Williams
Excel does not make use of all available RAM or Virtual memory.
This is independent of operating system (Win95/Win98/Win NT4.. have not
tested WIN2000 but I dont expect anything different)
for Excel 95, 97 and 2000 there are memory limits on various things:
- charts and graphic objects
- external links
- memory used by formulae and pivot tables hits the wall at about 80MB
This excludes memory used in your workbook by cells containing only data
no formulae),
so a workbook containing both data and formulae can use more than 80 MB. ( I
have not yet found a limit for data cells).
If this is an intermittent problem you may have a memory leak: usually this
is solved by saving your workbook, closing Excel and restarting,
There are memory leaks in some circumstances on:
- PageSetup
- replacing external link formulae
- some external data retrieval actions
- controls embedded on worksheets
You can track Excels internal use of memory using the worksheet function
INFO("memused"), or Application.MemoryUsed from VBA.
these limits are at the application level so apply across all the workbooks
and add-ins you currently have open.
Try removing all add-ins from excel, closing all other workbooks and then
opening the problem book.
If you can open it you can reduce the amount of memory used by formulae by:
- using multi-cell array formulae or formulas stored in names where you have
repetitive formulae.
- finding ways of shortening the formulae
HTH
Charles
____________________________________
Decision Models Ltd,17 Binswood Avenue,
Leamington Spa,Warks CV32 5SE, UK
Tel: (44)01926-334289 Fax: (44)01926-881487
Greg Cocks
Thanks for that. I am going to look into the information that Charles and
yourself were good enough to provide (along with that, such as it is, at
http://support.microsoft.com/support/kb/articles/Q99/3/45.ASP) and see what I
can come up with.
My thoughts now are to take the 21 data sets that contribute to the two charts
and move them and their calcs to 21 separate Excel files. In the 22nd I will
reconstruct the two charts and link the data in one by one. Think this will
work?
Again, thank you...
Regards,
GREG...
----------------------------------------------
David McRitchie wrote:
> Hi Greg,
> Do you have a lot of graphics/shapes/buttons these use Graphical
> resources.
>
> Look at your Tasks and see how much memory Excel itself is using.
> Someone calls himself Gozo pointed
> out that Excel can only go up to about 64MB, and
> this can happen when you have like 8000 x 256 cells dependent
> on the value in one cell. i.e. each cell is valued at the 1 + value of
> the previous cell.
>
> HTH,
> David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
> My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
>