Home |
Search |
Today's Posts |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a range to an array
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Read Worksheet Data into VBA Array? | Excel Discussion (Misc queries) | |||
Excel, read in an array | Setting up and Configuration of Excel | |||
Read comboboxes into array | Excel Programming | |||
Read in Array | Excel Programming | |||
Read Range Data into Array | Excel Programming |