Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a range to an array
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a range to an array
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 "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a range to an array
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. |
#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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read a range to an array
Don't do it then.
-- Regards, Tom Ogilvy "Microsoft Forum" wrote in message ... 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. |
Reply |
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 |