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)



"Charles" wrote in message
...
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/lib...s2(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