Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: What does Redim Preserve do in term of memory
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: What does Redim Preserve do in term of memory
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. -- HTH... Jim Thomlinson "Charles" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does Redim Preserve do in term of memory
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does Redim Preserve do in term of memory
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. -- HTH... Jim Thomlinson "Chip Pearson" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does Redim Preserve do in term of memory
"Chip Pearson" wrote in message:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does Redim Preserve do in term of memory
I see. Thanks for your answers!
Charles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA syntax help: ReDim Preserve an array | Excel Discussion (Misc queries) | |||
redim preserve the second dimension in a two-dim array | Excel Programming | |||
redim preserve | Excel Programming | |||
Redim Preserve doesn't work | Excel Programming | |||
ReDim, Preserve and Multidimensional arrays | Excel Programming |