[RESOLVED] [Excel VBA] Dim an Array

Can you help me understand what's going on with arrays. For example, if I use the following code (pay attention to the dim and redim for the array)

Code:

---

Sub test3()
    Dim DataArray() As Variant
    ReDim DataArray(1 To 7, 1 To 5)
    Dim i As Long
   
    For i = 1 To 30000
        DataArray = Worksheets("Sheet1").Range("E25:I31").Value
    Next i
End Sub

---

everything works just fine. But, if I try to replace the two dim/redim lines for the array with

Code:

---

    Dim DataArray(1 To 7, 1 To 5) As Variant

---

now the code no longer works, error: "Can't assign to array". However, I think if I did a loop and assigned one value at a time, that would work. Or, if I just do the one statement

Code:

---

    Dim DataArray As Variant

---

that is, I never give the dimensions, or even put () to show it's an array, then I can get data from a range in Excel all at once. And, with limited testing, it seems to be about the same speed as using the dim/redim statements together, but the code isn't quite as clear. Can any one help me understand what's going on here?

Thanks

this creates a dynamic array:

Code:

---

Dim DataArray() As Variant
ReDim DataArray(1 To 7, 1 To 5)

---

the array can be resized up or down as needed... think of it as a piece of rubber that can be stretched.


while this creates a static array:

Code:

---

Dim DataArray(1 To 7, 1 To 5) As Variant

---

meaning it's size cannot be changed...It's like a square of glass... it is what it is.

the reason it doesn't work is because the Range wants to return a dynamic array... because it doesn't really know how big of a matrix it is going to need to return. Odds are, you can probably even remove the redim... maybe...

there's nothing wrong with your code there... in fact, it's probably the preferred way to get the data out of the range into an array.

-tg

Okay, that makes sense. Thanks for your help!