You can only redim a dynamic array. Dimming it as x(50) makes it static.

With Redim Preserve only the last dimension can be changed. So make your
array "horizontal": Redim Arr(1,50).


--
Jim Rech
Excel MVP
"Fred" wrote in message
...
|
| I have an Array that needs to be filled with several values.
| I do not know beforehand how many values will be necessary, so I Dim it to
| 50 values to be on the safe side and Redim it to the actual amount after
| calculation (10 in this example)
| Logical thinking says I should do that the following way:
|
| 1. Dim Arr(50)
| 2. Fill array with values (after which necessary dimension is known)
| 3. Redim Preserve Arr(10)
|
| This code gives me the error that the dimension of the matrix is already
| set. So I replaced the first line with Dim Arr(), followed by ReDim(50) as
| shown in the follwing code (which works):
|
| 1. Dim Arr()
| 2. Redim Arr(50)
| 3. Fill array with values (after which necessary dimension of 10 is known)
| 4. Redim Preserve Arr(10)
|
| My first question is: What's wrong with the first section of code ?
|
|
| OK, So this seems to work now, BUT I actually need a 2 column
| multidimensional Array, so I thought this should work:
|
| 1. Dim Arr()
| 2. Redim Arr(50,1)
| 3. Fill array with values (after which necessary dimension of 10 is known)
| 4. Redim Preserve Arr(10,1)
|
| But, you guessed, I get an error (nr. 9, out of range) on the "ReDim
| Preserve Arr(10, 1)" statement in the example code below. Can anyone tell
me
| why this doesn't work ?
|
| Sub MDArrayTest()
|
| Dim i As Long
| Dim Arr()
|
| ReDim Arr(50, 1)
|
| For i = 0 To 10
| Arr(i, 0) = "Col 0: " & i
| Arr(i, 1) = "Col 1: " & i
| Next
|
| ReDim Preserve Arr(10, 1)
|
| For i = LBound(Arr) To UBound(Arr)
| Debug.Print i & " - " & Arr(i, 0) & " - " & Arr(i, 1)
| Next
|
| End Sub
|
|
| Thanx !
|
|