Read a range to an array

[Microsoft Forum]

Hi all,

I know the following code will work fine for me:

Function ArrayFromRange(Rng As Range) As Variant
ArrayFromRange = Rng
End Function

But I just want to know why the following alternative doesn't work,
especially why the REDIM statement is invalid here:

Function ArrayFromRange(Rng As Range) As Variant
Dim i As Long, j As Long
ReDim ArrayFromRange(Rng.Rows.count, Rng.Columns.count)
For i = 1 To Rng.Rows.count
For j = 1 To Rng.Columns.count
ArrayFromRange(i, j) = Rng(i, j)
Next
Next
End Function

By the way, if I wish a function to return an array, is declaring the
function as type "variant' the only way?

Thanks for your advice.

Frederick Chow
Hong Kong.

 

[Otto Moehrbach]

You say:
ReDim ArrayFromRange(Rng.Rows.count, Rng.Columns.count)
The lower bound of the array, as written, is Rng.Rows.Count.
The upper bound is Rng.Columns.Count.
The number of items in any array is (Upper Bound)-(Lower Bound).
IOW, the number of items in your array IAW the Redim statement you have is
equal to the number of columns less the number of rows. I don't think this
is what you want. HTH Otto

 

[Tom Ogilvy]

I can't say why, but I always cringe when I see a function name used as a
variable - nonetheless, it works in many cases. Apparently this isn't one
of those cased. Yes, in general I would say only a variant can be used to
pass an array (or a variant array in Excel 2000 or later).

This works:

Function ArrayFromRange(Rng As Range) As Variant
Dim i As Long, j As Long, v As Variant
ReDim v(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
v(i, j) = Rng(i, j)
Next
Next
ArrayFromRange = v
End Function

 

[Microsoft Forum]

Hi Tom,

So do you mean that I must declare a variant variable within a function, and
finally assign that (variant) array back to the function? Seems redundant to
me.

Frederick Chow

 

[Tom Ogilvy]

Don't do it then.

--
Regards,
Tom Ogilvy

Hi Tom,

So do you mean that I must declare a variant variable within a function, and
finally assign that (variant) array back to the function? Seems redundant to
me.

Frederick Chow