"Out of Memory" error

[CraigFarnden]

I have a VBA based model that creates very large arrays. It ran OK in nExcel
2002 and 2003, but the first time I tries to run it in Excel 2007 I got an
"Out of Memory" error. This appears to happen as memory is being allocated to
arrays when the simulation is first being initiated.

To try to isolate the problem, I created a very simple macro that does
nothing except create a 2-dimensional array:

Dim Array1() as single
Sub TestArray()
ReDim Array1(10000, 10000)
' Pause here using break point to check memory usage
in Task Manager
Erase Array1
End Sub

Increasing the array dimension too much above 10000 x 10000 results in the
"out of Memory" error. Prior to running the macro, Task Manager lists Excel
using about 27,000 K of memory.
Pausing the program part way with the array set at 10000 x 10000, Excel is
using 420,000 K. At 15000 x 11500, the memory usage was 740,000 K. At 15000
x 12000, I got the error.

I tried the same thing in Excel 2003 and got similar results, although with
a slightly larger array size before the error popped up.

It was my understanding that Excel 2002 and 2003 were limited to accessing a
maximum of 1 GB of RAM, while Excel 2007 was limited only by the physical
RAM. If that were the case, shouldn't my array size limits be much larger
running Excel 2007? Or is there still a memory size limit with VBA?

Old machine where my simulator worked: Pentium 4, 2 GB RAM, XP pro SP2,
Office 2003

New Machine where simulator encounters "Out of Memory" error: AMD Athlon
Dual Core, 4 GB RAM, XP Pro x64 SP2, Office 2007

Any thoughts?

 

[gimme_this_gimme_that]

Excel isn't the tool for handling two dimensional arrays of 10000 by
10000.

Use MatLab, Mathematica, S, or APL (or J for Excel).

Excel is a presentation package with a backend Visual Scripting
language: VBA.

As to the fact that it worked in 2002 but not in 2007. Yeah, there are
many subtleties associated with building a C++ DLL and having that DLL
use memory like you want it to.

It could be that the 2002 Excel engine was 16 bit and 2007 is 32 bit.

But who cares? Excel isn't the tool for that job.

 

[Harald Staff]

Excel is a presentation package with a backend Visual Scripting
language: VBA.

LOL. IT department talk.

Best wishes Harald

 

[Nigel]

I agree, I get this all the time from IT geeks.

--

Regards,
Nigel
(e-mail address removed)

 

[Charles Williams]

Available memory in Excel 2007 is supposed to be limited by Windows XP.
This gives a theoretical 2GB of virtual memory, as compared to Excel 2003
which had a limit of 1GB of working set memory.
However in practical terms as you have found there does not seem to be much
difference in the size of things you can do.
Part of this lack of real improvement is caused by differences in
terminology (virtual memory is not the same as working set memory), part by
changes in the way Excel 2007 uses memory and part by increased size of
indexes for the big grid.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com