VBA: What does Redim Preserve do in term of memory

[Charles]

Hello

a somewhat basic VBA question, but I can't find the answer on google.

What does Redim Preserve do in practice in term of memory. Does it
recopy every single element of an array in a new one with a different
dimension, as this article seems to say:

http://msdn2.microsoft.com/en-us/library/w8k3cys2(VS.80).aspx

or is it a bit more clever, and finds a way to add/remove dimensions
without recopying the data.

The question is only relevant in the context of large amounts of data.
What I am trying to figure out is if I have large amounts of data, is
it worth bother building a system of dynamic arrays with objects, or
is the Redim statement good enough.

Thanks in advance for your answer!
Charles

 

[Jim Thomlinson]

ReDim Preserve copies the existing array to a new location with a larger
memory allocation. So yes it has a whole pile of overhead. Redim just creates
the new larger memory allocation without copying the data from the existing
array so there is very little overhead.

If performance is a big concern then there are a couple of strategies to
deal with this.

One is to do some work to determine how many elements you will need ahead of
actually decalraing the size of the array. Then you only need to declare the
array once.
Another is to redim a much larger memory space than is necessary by adding
say 100 elements instead of just 1. You can then keep adding to the array
until all 100 of the extra spots have been used up at which point you would
need to onece again redim preserve 100 more spots. At the end when you know
how many spots you have actually used you can clean up the final array to the
correct size.

 

[Chip Pearson]

I believe Redim Preserve creates a new array with the specified bounds and
then does an element-by-element copy from the old array to the new array,
and then finally destroys the original array.

If you don't know how large the array needs to be when it is initially
allocated with ReDim, you can initialize it to some number of elements and
when that is exceeded, use ReDim Preserve to allocated some number of
additional elements, and the ReDim Preserve again when the resized array's
ubound is reached. One you are done filling the array, you can use a final
ReDim Preserve to trim off any unused elements that were allocated earlier.

For example, the following code initializes an array to 5 elements, and then
tries to fill up 20 elements. When the initial allocation is filled, a ReDim
Preserve is done to add new space to the array, in this case increasing the
size by 3 elements. A final ReDim Preserve is executed to chop off unused
elements from the end of the array.

Sub AAA()

Dim TheArray() As Long
Const C_INIT_SIZE As Long = 5
Const C_RESIZE_AMOUNT = 3
Dim UsedIndex As Long
Dim AllocatedToBound As Long
Dim N As Long
Dim Ndx As Long

'''''''''''''''''''''''''''''''''''''
' Size the array to some initial
' size and initialize the variables.
'''''''''''''''''''''''''''''''''''''
ReDim TheArray(1 To C_INIT_SIZE)
UsedIndex = 0
N = 0
Ndx = 0
AllocatedToBound = UBound(TheArray)

''''''''''''''''''''''''''''''''''''
' Load up the array with some values
''''''''''''''''''''''''''''''''''''
Debug.Print "-----------------------------"
For N = 1 To 20
Ndx = Ndx + 1
If Ndx > AllocatedToBound Then
AllocatedToBound = AllocatedToBound + C_RESIZE_AMOUNT
Debug.Print "REDIM: ", Ndx ' DEBUG ONLY
ReDim Preserve TheArray(1 To AllocatedToBound)
End If
TheArray(Ndx) = N * 10 ' or any value
Next N
Debug.Print "-----------------------------"
'''''''''''''''''''''''''''''''''''''''''
' Finally, trim off the unused elements
' of the array.
'''''''''''''''''''''''''''''''''''''''''
ReDim Preserve TheArray(1 To Ndx)

'''''''''''''''''''''''''''''''''''''''''
' Debug Only: See what's in the array.
'''''''''''''''''''''''''''''''''''''''''
For N = LBound(TheArray) To UBound(TheArray)
Debug.Print N, TheArray(N)
Next N

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Jim Thomlinson]

Kind of a picky point but I do not believe that the original array is
destroyed. The memory for the old array is just returned to the operating
system (never to be seen again) so there is no overhead to this operation
other than changing the array pointer from the old memory address to the new
memory address. Here is how I understand redim preserve...

1-XL requests memory from the OS (that is the necessary size for the new
array)
2-Memory is given to XL
3-XL copies the elements from the old array to the new array
4-XL changes the variable to point to the new array
5-XL gives back the memory associated with the old array and deletes the
pointer to that memory

Purely a techical thing but if I am off the bean somewhere I would
appreciate a lesson in what is really happening.

 

[Peter T]

I believe Redim Preserve creates a new array with the specified bounds and
then does an element-by-element copy from the old array to the new array,
and then finally destroys the original array.

My take is very slightly different in one small respect. From what I can
make out if Redim Preserve is used to increase the size of the last
dimension, exactly as Chip describes occurs. However if ReDim Preserve is
used to decrease the size then the lower portion of the array remains as
exactly was, in terms of memory address, and the upper portion freed from
memory.

On that basis, wherever viable I start with an oversized array and Redim
Preserve 'down' to required size when done, when the initial size is not
known say at start of some loop (along the lines both Chip and Jim
describe).

Regards,
Peter T

 

[Charles]

I see. Thanks for your answers!

Charles