Setting Array = Excel Range Values?

[Johnboy]

Is there a way to set the indices of an array equal to the values of cells in a range? Like this:

Dim myArray(1 to 2, 1 to 5) As Long
myArray = Range(Cells(1,1), Cells(2,5)).Value

I can't get the above code to work. I can easily go the other way:

Range(Cells(1,1), Cells(2,5)).Value = myArray

Is there a trick to it, other than simply looping through each cell and storing the value in the corresponding array index? Any help would be appreciated.

[RobDog888]

No, no shortcut.
Since your array is a long you may not want to depend on the default values
for the cells. Explicitly code out the entire objects and then convert them to
a long to match your array.

[Johnboy]

The As Long declaration was arbitrary. The actual application in which I wish to use this concept contains only numerical values that will be treated as double precision.

I just discovered that the following does work:

Dim myArray as Variant
Range(Cells(1, 1), Cells(2, 5)).Value = 123
myArray = Range(Cells(1, 1), Cells(2, 5)).Value
Range(Cells(3, 1), Cells(4, 5)).Value = myArray

After executing the above code the values in the first range are rewritten into the second range. Unfortunately this doesn't work if I dimension the myArray variable as an array, even if the dimensions of the array exactly match the size of the range. I don't understand this behavior. Why would it work in one direction (dimensioned array to range) but not in the other (range to dimensioned array)? I guess it's just one of those things. :confused: