Set the elements of an array vertically to the cells

[Rez]

Hello,

I have written Excel Function which returns an array:

Public Function dummy() As Variant
Dim arr As Variant

arr = Array(10, 20, 30)

dummy = arr
End Function

Now, I want to use my function in Excel but I want it to set the array
elements vertically in the cells. For instance if I am in the Cells(1,
1) and write
=dummy()
I am expecting to see
10
20
30
respectively in Cells(1, 1), Cells(1, 2), Cells(1, 3)
it will be greatfully appreciated if you have a solution for it.
Thanks,

 

[Tom Ogilvy]

You would have to select cells A1:A3 and enter =Dummy() and finish with
Ctrl+Shift+Enter to array enter the formula

In your function you would need

Public Function dummy() As Variant
Dim arr As Variant

arr = Array(10, 20, 30)

dummy = Application.Transpose(arr)
End Function

 

[Daniel.M]

Hi,


Tom already answered you.

I am expecting to see
10
20
30
respectively in Cells(1, 1), Cells(1, 2), Cells(1, 3)

Please note :
The first dimension of Cells is the row : Cells(Row, Column).
A 2 dimension VBA array has the row as its 1st dimension : Array(row,column)
A 1 dimension VBA array corresponds to an horizontal array (column-wise),
and that's why you have to TRANSPOSE it.

Regards,

Daniel M.