Creating a User Defined Array Function

[Jeff Mason]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Jeff Mason]

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?

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Jeff Mason]

Thanks again. This should be all I need to get up and running.