CODEWITHSUNDEEP
arraysvbaexcelexcel-2016
quant
quant

Reputation: 23052

Returning a 1D array in Excel 2016

I've been trying to figure out how to return arrays from VBA functions. I came across this site: http://www.cpearson.com/excel/returningarraysfromvba.aspx which provides some useful examples, notably this one:

Function Test() As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long
    ReDim V(1 To 3, 1 To 4)
    For R = 1 To 3
        For C = 1 To 4
            N = N + 1
            V(R, C) = N
        Next C
    Next R
    Test = V
End Function

When I type =Test() into vertical range of cells with ctrl+shift+enter I get the expected result:

1
5
9

But what if I want to return a 1D array? I tried modifying the function to make V a 1D array:

Function Test() As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long
    ReDim V(1 To 3)
    For R = 1 To 3
        For C = 1 To 4
            N = N + 1
            V(R) = N
        Next C
    Next R
    Test = V
End Function

Now when I enter this into the same vertical range I get this:

4
4
4

Why is this happening, and where is this behaviour documented?

Upvotes: 0

Views: 169

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

First TRANSPOSE() the 1-D array:

Function Test() As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long
    ReDim V(1 To 3)

    For R = 1 To 3
        For C = 1 To 4
            N = N + 1
            V(R) = N
        Next C
    Next R
    Test = Application.Transpose(V)
End Function

Then in the worksheet select three vertical cells and array enter:

=test()

enter image description here


Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Upvotes: 3

Rosetta
Rosetta

Reputation: 2725

because it is defaulted to fill across columns instead of rows.

try

=TRANSPOSE(Test())

if you want to fill down the rows.

if you want to change the default programably, you can also do 

Function Test()
    .
    .
    .
    Test = Worksheetfunction.Transpose(V)
End Function

Upvotes: 1

Related Questions