Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
I am having trouble trying to use ReDim on a 2 dimensional array.
I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
You can change the size of only the last dimension.
http://msdn.microsoft.com/en-us/libr...s2(VS.80).aspx -- If this post helps click Yes --------------- Jacob Skaria "RocketRod" wrote: I am having trouble trying to use ReDim on a 2 dimensional array. I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
Thanks but...
According to Alan Beban in his post of 14th March 2008 as a reply under subject "array dimensioning" he says the following which suggest that either dimension can be changed. Is he right? he writes: A bit more needs to be said. If Arr is a true variant() type array [e.g., Dim Arr() ReDim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change only the upper bound of the last dimension [e.g., Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] In this case, if you try to change either bound of the 1st dimension, or the lower bound of the 2nd dimension, you will get a Subscript out of range error. If, however, Arr is a an array contained within a Variant type variable [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2)] then with the Preserve keyword you can change either or both bounds of the last dimension [e.g., Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). "Nigel" wrote: Using Preserve prevents you from changing any dimension other than the last. Dim myArray(5,5) can be resized ReDim Preserve myArray(5,10) but not ReDim Preserve myArray(10,5) HTH -- Regards, Nigel "RocketRod" wrote in message ... I am having trouble trying to use ReDim on a 2 dimensional array. I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
see my reply to Nigel - I am led to believe otherwise but it may be wrong
"Jacob Skaria" wrote: You can change the size of only the last dimension. http://msdn.microsoft.com/en-us/libr...s2(VS.80).aspx -- If this post helps click Yes --------------- Jacob Skaria "RocketRod" wrote: I am having trouble trying to use ReDim on a 2 dimensional array. I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
I don't think you're reading that response quite right. He is saying that it
is possible to change the UPPER or LOWER bounds of the SECOND dimension in a specific case. You still can't change the bounds of the first dimension of the array. As he points out, there are functions out there that will redimension multi-dimensional arrays while preserving their contents. It's just that those functions are not built in to Excel. HTH, Eric Dim Arr() Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 1 to 4)] <-- Only upper bound of 2nd dim is changed! Dim Arr Redim Arr(1 to 10, 1 to 2) ReDim Preserve Arr(1 to 10, 0 to 3)] <-- Upper and lower bounds are changed! If the functions in the freely downloadable file at http:/home.pacbell.net/beban are available to your workbook, then you can use the ResizeArray function to preserve the values of the array that is passed to it and change the lower and/or upper bounds of any or all of the dimensions of a one-, two- three- or four-dimensional array, or increase (up to 4) or decrease the number of the array's dimensions (whether or not the array is contained within a Variant type variable). |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
Please read that carefully...."you can change both bounds of the last
dimension" Again it is the last dimension///// -- If this post helps click Yes --------------- Jacob Skaria "RocketRod" wrote: see my reply to Nigel - I am led to believe otherwise but it may be wrong "Jacob Skaria" wrote: You can change the size of only the last dimension. http://msdn.microsoft.com/en-us/libr...s2(VS.80).aspx -- If this post helps click Yes --------------- Jacob Skaria "RocketRod" wrote: I am having trouble trying to use ReDim on a 2 dimensional array. I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Redim on 2 dimensional array
You can only change the last dimension. I has to do with the way that the
data is stored. A 2d array (or multi d for that matter) is stored on the disk as a long string of memory. Here is an example: A 2,4 array is actually stored as 4 blocks of 2. So when you redim the array to 5 to then it just adds another block of 2 to the end (not strictly true as it actually creates an an empty 2,5 array and then copies the old array over). To redim the array to 3,4 would mean inserting an extra item into each block of 4. That would require a whole pile of shuffling of memory to create all of the required new memory slots. The overhead is just too great so it won't do it. -- HTH... Jim Thomlinson "RocketRod" wrote: I am having trouble trying to use ReDim on a 2 dimensional array. I need to increase the size of the array in a For€¦Next loop as data is determined as valid - it is a staff list by name with 3 additional columns of data for each name but the number of staff that will populate the array is not known at the start, hence the Array needs to start as a 1x4, then change to 2x4 etc I have tried using both types of Dim statements as follows based on some of the forum references Dim StaffArray() and Dim StaffArray() €˜apparently this allows both dimensions of the array to be modified in the ReDim with each of the following types of ReDim (obviously only one at a time) Dim staffindex as Integer €¦ For€¦€¦.€¦ ReDim Preserve StaffArray(5, 4) ReDim Preserve StaffArray(staffindex, 4) ReDim Preserve StaffArray(staffindex To 5, 4) ReDim Preserve StaffArray(staffindex To 5, 4 To 4) Next€¦ Can some give me the right combination of Dim and ReDim please - I keep getting subscript out of range errors |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
Export 1-dimensional array values to a two-dimensional table? | Excel Programming | |||
Changing a two-dimensional, one row array to one-dimensional | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
Create One-Dimensional Array from Two-Dimensional Array | Excel Programming |