[RESOLVED] [Excel VBA] Dim an Array

[ActSciMan]

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)

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

    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

    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