Erase Array correctly (erase doesn't work?)

Question

I'm trying out some limitations (according to memory) in Excel (2010, 32bit), somehow Erase Array doesn't work correctly.

Sub ArrDims()
    Dim Arr()
    Dim lngC1&, lngC2&

    With Sheet1
        Arr = .Range(.Cells(1, 1), .Cells(1000000, 20))

        For lngC1 = LBound(Arr, 1) To UBound(Arr, 1)
            For lngC2 = LBound(Arr, 2) To UBound(Arr, 2)
                Arr(lngC1, lngC2) = lngC1 + lngC2
            Next lngC2
        Next lngC1
    End With

    With Sheet2
        .Cells(2, 2).Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr
        .Cells.ClearContents
    End With

    Erase Arr

End Sub

Starting Excel: approx 23MB of my RAM is used by xl

Execute the sub (with/without) Erase Arr: approx 210MB of my RAM is used by xl

Another execution of the sub isn't possible due to "Out of memory".

Is there away to delete this allocation?

After your sub execution the memory is used by 20000000 cells, containing empty value. `Sheet2.UsedRange` hints at it. If you delete Sheet2 or part of the rows, you see that memory is released. — omegastripes, Sep 14 '15 at 19:16
Ok. Replacing `.ClearContents` by `.Clear` reduces the used memory (acc to TaskManager) from 210 to the initial 25 ONLY IF i use `.Usedrange` (explicitly say `Debug.Print Sheet2.Usedrange.Address` or save the address in a string or what ever. Strange. This leads me to the next question: When I execute (Usedrange is set to A1 by `.Clear`) the sub again, I run out of memory. So the UsedRange wasn't really set to nothing? — dan, Sep 17 '15 at 12:44

score 0 · Answer 1 · answered Sep 14 '15 at 10:23

0

Simply delete by redim it

ReDim Arr(1)

This will clear all data in Arr() and set size of Arr() to 1.

answered Sep 14 '15 at 10:23

Adisak Anusornsrirung

690
8
14

When the sub is executed and ended (`End Sub`) Excel still uses 210 RAM, `Erase Arr` or `ReDim Arr(1)` doesn't matter. – dan Sep 14 '15 at 10:36
Try to put "End" before End Sub – Adisak Anusornsrirung Sep 14 '15 at 10:51
Still approx. 210MB used (according to Windows Task Manager) – dan Sep 14 '15 at 10:56

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

as you don't type your variable Arr, VBA consider it is an object. Type your variable and create another one:
Dim Arr() as variant Dim rng as Range
then :

set rng =  .Range(.Cells(1, 1), .Cells(1000000, 20))  
Arr = rng  
'you code

erase Arr set rng= nothing

Or you can just try :

set Arr=nothing

without modifying your code

have a look to this intersting post may help you :
When should an Excel VBA variable be killed or set to Nothing?

edited May 23 '17 at 12:14

Community

1
1

answered Sep 14 '15 at 11:42

scraaappy

2,830
2
19
29

I agree with your suspicions in that it is a potential COM reference count issue. I would support your code if you'd be more explicit about extracting the values using the rng.Value2 method. – S Meaden Sep 14 '15 at 12:46
Thought it is recommended to erase the arrays. But the problem is (have a look at my first post - comments) the usedrange by xl. Thx for your help, but I just wanted to know how to erase all correctly ("reset" excel, when actually nothing was done which can be seen) – dan Sep 17 '15 at 12:48

score 0 · Answer 3 · answered Sep 14 '15 at 12:55

Ok, so it is true that it is faster to operate on a big chunk of data than to loop through cell by cell BUT you asking for 20 million cells. For each cell it costs 16 bytes minimum because it is close to a COM_VARIANT or VBA Variant and this is before any strings are allocated!

So whilst I agree with doing chunks perhaps you should consider using smaller chunks.

Erase Array correctly (erase doesn't work?)

3 Answers3