S/sheet memory leak

[mathare]

I think I have a problem with my spreadsheet that I use to record all my betting. When I run it on my laptop at work (XP Pro, Office 2003) the memory usage (as shown in Task Manager) keeps ticking up all the time I have the spreadsheet open. Other day after having it open for a few hours it had sucked up 1.2GB of my memory and I think that was stopping me successfully saving the file.

It also seems to be using 60+% CPU when it's not supposed to be doing anything (no macros running, it's just sat there in the background).

Any thoughts anyone? There are no volatile macros/functions in the project but something is eating up my CPU and RAM.

I haven't noticed this at home but that's not to say it isn't doing it there too.

[Win2Win]

Upgrade it :)

[mathare]

Upgrade it :)What? The laptop, spreadsheet or Office?

[Win2Win]

Yes :)

[mathare]

Not an option I'm afraid

[Win2Win]

Try a Memory Manager....search Google......also have you the latest Office updates off MS?

[GlosRFC]

There's a host of reasons why Excel is using up memory - not least because the program has a built in memory "manager". Funnily enough, this in-built manager actually prevents Excel from using all of your available RAM. Excel 2003 is limited to 1GB of memory usage and out of memory errors/leaks are usually caused by copying large quantities of data or adding formula to large spreadsheets (http://support.microsoft.com/?kbid=313275).

Other memory leaks tend to revolve around printer issues, such as pagesetup (http://support.microsoft.com/default.aspx?scid=kb;en-us;192869) or using certain printer drivers (http://support.microsoft.com/kb/q165985/), or it might be that your spreadsheet is referencing external workbooks or using a lot of pivot tables.

The solution to most memory leaks is to close Excel and restart. You can also tweak the size of your swapfile to make Excel more efficient - with Office 2003/XP you should be looking at a minimum swapfile size of 512Mb. Another thing to try is to save a new copy of your spreadsheet using File, Save As - this might help strip out any redundant code thus making the spreadsheet filesize smaller.

You say that it's using the CPU "even though it's not doing anything" yet it probably is doing stuff - do you have automatic calculation turned on or Autorecover which is saving your spreadsheet every 10 minutes or so? Of course, there will also be other background processor (kernel) tasks running that Excel has to take account of with its graphical display, e.g. mouse/video drivers etc. which can also be causing its CPU usage to spike. If you look at the Performance tab in Task Manager, check out the kernel memory total. If it's close to the physical memory total, then this implies that your OS is concentrating on handling these kernel tasks and isn't anything to worry about.

Finally, you can use Excel itself to check how much memory is being used/available with the INFO function. For example =INFO("memused") tells you how much memory that spreadsheet is using. =INFO("memavail") tells you how much total memory you have left. Other strings you can get INFO to check out are:

directory - current path to the spreadsheet
osversion - what operating system you're using
recalc - whether you're using automatic or manual calculation
release - which version of Excel you have
totmem - total memory available for use
numfile - number of active worksheets

[mathare]

Autorecover is off. Auto calculation is on but that shouldn't affect anything if the spreadie isn't doing anything should it?

No pivot tables. No external references.

It seems to have calmed down massively now. It could have been that I was unwittingly accessing it off my USB pen drive before.

I'll try the =INFO() function out properly sometime but just now Excel told me it couldn't complete it properly.

Cheers Glos

[mathare]

Now it's gobbling up memory again :(

=INFO("memused") returns 112051500. That's presumably bytes and sounds like a lot to me. 106.9MB - that a lot for one spreadsheet? It's around twice the file size on disk.

My paging file is 4000MB. Surely that's big enough.

My total physical memory is 2096360 with total kernel memory of 79480 (ish).

Any further diagnosis Dr Glos?