-
Jul 14th, 2011, 08:45 AM
#1
Thread Starter
Member
[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
-
Jul 14th, 2011, 09:53 AM
#2
Re: [Excel VBA] Dim an Array
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
-
Jul 14th, 2011, 10:09 AM
#3
Thread Starter
Member
Re: [Excel VBA] Dim an Array
Okay, that makes sense. Thanks for your help!
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|