Why Dim Arrays as Variants?

[hahdawg]

Suppose I have a bunch of random numbers in A1:C5000, and I want to create an array, call it NumArray, of these numbers.

This code works:

Dim NumArray As Variant
NumArray=Range("A1:C5000").Value.

.

But this code doesn't work:

Dim NumArray(1 to 5000, 1 to 3) As Double
NumArray=Range("A1:C5000").Value.

.

Why can't I dim NumArray as a double? I understand that I could dim it as a double, and loop through the range to fill it, but that would be a lot slower. I'm just trying to understand what's going on here.

 

[shadow12345]

You can use ReDim with double.... this page has an explaination of sorts but its a bit over my head

http://www.cpearson.com/excel/vbaarrays.htm

 

[xenou]

Dim NumArray As Variant
NumArray=Range("A1:C5000").Value

Your variant array will contain numbers of subtype double. This is still the fastest way to do this. I'm not sure of the technical reasons. Since we are interfacing between VBA and the Excel worksheet I suppose it's in the way that the values are retrieved and mapped into the array, but your guess is as good as mine.