Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Hi,
I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Jeff,
Here is a very simple example, but I think you will need more Sub test() Dim myArray myArray = LoadArray Debug.Print myArray(1) Debug.Print myArray(2) Debug.Print myArray(3) End Sub Function LoadArray() Dim ary(1 To 3) ary(1) = "Item 1" ary(2) = "Item 2" ary(3) = "Item 3" LoadArray = ary End Function -- HTH RP (remove nothere from the email address if mailing direct) "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Public Function ReturnArray() as Variant
ReturnArray = Array(1,2,3,4) End Function if =ReturnArray() is array entered in C2:F2 (as an example), the values 1, 2, 3, 4 are displayed. -- Regards, Tom Ogilvy "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Thanks Tom. Interestingly, if you select multiple rows in a column for the
array function, the first value of the array is repeated in each cell. Only when multiple columns in a row are selected do you see each array value. Is there a way to make the function flexible so that the function can occupy either a row or a column? "Tom Ogilvy" wrote in message ... Public Function ReturnArray() as Variant ReturnArray = Array(1,2,3,4) End Function if =ReturnArray() is array entered in C2:F2 (as an example), the values 1, 2, 3, 4 are displayed. -- Regards, Tom Ogilvy "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Jeff,
It's because it defaults as a 2 dimensional array, and Tom just loaded the first element of the row dimension. You can transpose it with =TRANSPOSE(returnarray()) again array entered into say A1:A4 -- HTH RP (remove nothere from the email address if mailing direct) "Jeff Mason" wrote in message ... Thanks Tom. Interestingly, if you select multiple rows in a column for the array function, the first value of the array is repeated in each cell. Only when multiple columns in a row are selected do you see each array value. Is there a way to make the function flexible so that the function can occupy either a row or a column? "Tom Ogilvy" wrote in message ... Public Function ReturnArray() as Variant ReturnArray = Array(1,2,3,4) End Function if =ReturnArray() is array entered in C2:F2 (as an example), the values 1, 2, 3, 4 are displayed. -- Regards, Tom Ogilvy "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
No,
it defaults to a 1 Dimensional horizontal array and Tom loaded the entire array. As the OP said, it works fine with a horzontal range of cells (1 row, multiple columns). In the function you could also use Transpose Public Function ReturnArray() as Variant Dim rng as Range set rng = Application.Caller if rng.columns.count 1 then ReturnArray = Array(1,2,3,4) else ReturnArray = Application.Transpose(Array(1,2,3,4)) End if End Function Further checks could be added to insure it is a range of either one column or one row. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Jeff, It's because it defaults as a 2 dimensional array, and Tom just loaded the first element of the row dimension. You can transpose it with =TRANSPOSE(returnarray()) again array entered into say A1:A4 -- HTH RP (remove nothere from the email address if mailing direct) "Jeff Mason" wrote in message ... Thanks Tom. Interestingly, if you select multiple rows in a column for the array function, the first value of the array is repeated in each cell. Only when multiple columns in a row are selected do you see each array value. Is there a way to make the function flexible so that the function can occupy either a row or a column? "Tom Ogilvy" wrote in message ... Public Function ReturnArray() as Variant ReturnArray = Array(1,2,3,4) End Function if =ReturnArray() is array entered in C2:F2 (as an example), the values 1, 2, 3, 4 are displayed. -- Regards, Tom Ogilvy "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a User Defined Array Function
Thanks again. This should be all I need to get up and running.
"Tom Ogilvy" wrote in message ... No, it defaults to a 1 Dimensional horizontal array and Tom loaded the entire array. As the OP said, it works fine with a horzontal range of cells (1 row, multiple columns). In the function you could also use Transpose Public Function ReturnArray() as Variant Dim rng as Range set rng = Application.Caller if rng.columns.count 1 then ReturnArray = Array(1,2,3,4) else ReturnArray = Application.Transpose(Array(1,2,3,4)) End if End Function Further checks could be added to insure it is a range of either one column or one row. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Jeff, It's because it defaults as a 2 dimensional array, and Tom just loaded the first element of the row dimension. You can transpose it with =TRANSPOSE(returnarray()) again array entered into say A1:A4 -- HTH RP (remove nothere from the email address if mailing direct) "Jeff Mason" wrote in message ... Thanks Tom. Interestingly, if you select multiple rows in a column for the array function, the first value of the array is repeated in each cell. Only when multiple columns in a row are selected do you see each array value. Is there a way to make the function flexible so that the function can occupy either a row or a column? "Tom Ogilvy" wrote in message ... Public Function ReturnArray() as Variant ReturnArray = Array(1,2,3,4) End Function if =ReturnArray() is array entered in C2:F2 (as an example), the values 1, 2, 3, 4 are displayed. -- Regards, Tom Ogilvy "Jeff Mason" wrote in message ... Hi, I'm trying to create a function that returns an array of values. I can't figure out the code to make this work. Does anyone have any insight? Your help would be appreciated. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating my own user defined function help statements | Excel Worksheet Functions | |||
Array of 100X250 as return of a user defined function? | Excel Programming | |||
Find size of array passed to user-defined function | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming | |||
User-defined function creating circular reference | Excel Programming |