Hi,
I have a macro that reads in some large text files (to exceed Excel's 65k
line limit).
The macro has a couple of arrays dim'd at 300,000 rows x 57 columns.
Most of the elements are strings so that adds to the size.

I got an Error 7 out of memory issue so I've been running some tests to see
what could be my limitation - see below for test.

I set up the test to redim the arrays in larger and larger incriments to see
what incriments use how much memory. Then I started adding strings to each
element once they were all created.

I get through the redimming and my Excel Memory goes up to about 600+Meg.
After I get into the assigning text to all elements, the CPU utilization
dropps to about 5-15% and the memory drops down to under 30Meg. I find this
very strange because I can stop the macro in vba and look at the "locals"
window and see that the arrays all have the values stored in each element.

So, why does memory show only 30Meg? Or is this really 1030 Meg? And does
the CPU drop because it's now going to the hard drive?

Anyway, just curious.
THanks,
MikeZz

Here's my test Sub:

Dim step, cols, multi, mems

cols = 57
multi = 100000

step = 1
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal


step = 2
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal

For r = 1 To multi * step
For c = 1 To cols
arr1(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

For r = 1 To multi * step
For c = 1 To cols
arr2(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

For r = 1 To multi * step
For c = 1 To cols
arr3(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

step = 3
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal

End Sub

Check out this link on memory...

http://www.decisionmodels.com/memlimits.htm
--
HTH...

Jim Thomlinson


"MikeZz" wrote:

Hi,
I have a macro that reads in some large text files (to exceed Excel's 65k
line limit).
The macro has a couple of arrays dim'd at 300,000 rows x 57 columns.
Most of the elements are strings so that adds to the size.

I got an Error 7 out of memory issue so I've been running some tests to see
what could be my limitation - see below for test.

I set up the test to redim the arrays in larger and larger incriments to see
what incriments use how much memory. Then I started adding strings to each
element once they were all created.

I get through the redimming and my Excel Memory goes up to about 600+Meg.
After I get into the assigning text to all elements, the CPU utilization
dropps to about 5-15% and the memory drops down to under 30Meg. I find this
very strange because I can stop the macro in vba and look at the "locals"
window and see that the arrays all have the values stored in each element.

So, why does memory show only 30Meg? Or is this really 1030 Meg? And does
the CPU drop because it's now going to the hard drive?

Anyway, just curious.
THanks,
MikeZz

Here's my test Sub:

Dim step, cols, multi, mems

cols = 57
multi = 100000

step = 1
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal


step = 2
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal

For r = 1 To multi * step
For c = 1 To cols
arr1(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

For r = 1 To multi * step
For c = 1 To cols
arr2(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

For r = 1 To multi * step
For c = 1 To cols
arr3(r, c) = "XXxxxxxxxxxxxxXXXXXXXXXX"
Next c
Next r

step = 3
ReDim arr1(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr2(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal
ReDim arr3(1 To multi * step, 1 To cols) As Variant
mems = Application.MemoryTotal

End Sub