Solved: Excel out of memory with plenty of memory unused

[GreenTree]

I'm writing VBA code to work with a spreadsheet that deals with a lot of data, and it's using a lot of memory. Currently, the saved spreadsheet is about 76MB, and pretty much all of it gets read into arrays in memory for various manipulations. At times, I get Visual Basic "out of memory" error messages, and when I use the Application.MemoryUsed and Application.MemoryAvailable functions, I show about 1,000,000 bytes available and about 84,000,000 bytes used. At the same time, though, when I use Ctrl-Alt-Delete to look at my SYSTEM memory, I have lots free... Excel is using about 500 MB, and I have over that much physical memory showing as available (I have 2GB ram on the computer).

So I'm thinking that there's a limit in Excel that I'm really close to, 84,000,000 used and 1,000,000 available, and I start to write this post to see if there's a way to allocate more of my physical memory so that Excel can use it, since I'm not done doing all the things I want to do with this data. HOWEVER, when I try to generate the exact "out of memory" error by loading an older (but still large) version of the spreadsheet, to my surprise I do NOT get an out of memory error, but instead my "in use" memory in Excel shows 155,000,000+ bytes, and the "available" memory shows THE SAME 1,048,576 bytes available. Huh.

Am I IN FACT close to any limits for memory, or will Excel actually use whatever the system has available and I'll only get errors when I have other programs in use hogging resources? Is Application.MemoryAvailable a bogus indicator? Should I be worried?

Thanks!

G.T.

[GreenTree]

Anyone?

Is Application.MemoryAvailable a valid indicator of what's really available to Excel, or no?

Thanks!

[johnske]

• AFAIK, Excel can access all the free available RAM it needs to accomplish a task, but what's available also depends on whatever other tasks are utilizing resources.

• If you open a number of workbooks and close them, they are still in system memory and using some available RAM until you completely Quit the excel application. I would imagine that this also applies to a number of saved instances of the same file, i.e. they would be treated as 'different' workbooks (although I can't say this for certain).

HTH

[Wolfgang]

Hi...

Here you will find heaps of information regarding your "annoyance"...

Best,
Wolfgang


http://www.decisionmodels.com/memlimitsc.htm

[GreenTree]

Wolfgang,

Great site! Many thanks. One of the links on that page has the following:

You can track the amount of memory Excel is using for workbooks etc, excluding memory occupied by the Excel programs etc., using:

• The FastExcel Memory Used, Pivot Cache Memory Used and Profile Workbook commands.
• Excel’s worksheet function INFO("memused")
• Application.MemoryUsed from VBA.

There are two other memory used commands available, but they are not useful:

• INFO("memavail") or Application.MemoryFree
• INFO("totmem") or Application.MemoryTotal

These are supposed to give you the amount of memory available or free, and the sum of memory used and memory available.
Unfortunately memory available or free does not work properly and always shows a constant 1 megabyte available.
Consequently total memory, which shows the sum of memory used and memory free, also does not work properly.

That answers my question, and I'm marking the thread solved. Thanks again.



G.T.