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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Microsoft Forum" wrote in message
...
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 he

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.