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 !

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 !
|
|

Fred,

My first question is: What's wrong with the first section of code ?

In the VBE Help, do a search on "declaring arrays". Select the "Declaring
Arrays" item.
In short, there are two types or arrays: 1) Static; 2) Dynamic.

Redim can only be used with Dynamic arrays:
Dim arrTest()


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 ?

In the VBE Help, do a search on "redim".

The short answer is, if you use ReDim Preserve you can only redim the last
dimension of a multidimensional array. For example:
Dim arrTest(x,y,z)

You can change z, but not x or y.

Possible solution is to flip your array dimensions. Change Arr(x,y) to
Arr(y,x).

Troy

Jim,

thanks for your quick response. I get the idea now with static and dynamic.
As for the ReDim Preserve: I changed the code and it works perfectly !

Thanx a lot !


"Jim Rech" wrote in message
...
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 !
|
|

Troy,

thanks, for your quick response. It works fine now !


"TroyW" wrote in message
...
Fred,

My first question is: What's wrong with the first section of code ?

In the VBE Help, do a search on "declaring arrays". Select the "Declaring
Arrays" item.
In short, there are two types or arrays: 1) Static; 2) Dynamic.

Redim can only be used with Dynamic arrays:
Dim arrTest()


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 ?

In the VBE Help, do a search on "redim".

The short answer is, if you use ReDim Preserve you can only redim the last
dimension of a multidimensional array. For example:
Dim arrTest(x,y,z)

You can change z, but not x or y.

Possible solution is to flip your array dimensions. Change Arr(x,y) to
Arr(y,x).

Troy