How to return array() in vba function to use it in

Question

I'm writing a function in VBA to use in excel formula, it's ok if my function return a single value:

=MYVALUE(A1)

Now I wrote another function which returns an Array(1,2,3,4,...) and I replace my excel formula by Array formula:

{=MYARRAY(A1)}

But when I stretch the formula, all cells display the first value of my array. why ?

Here is my VBA source code (complement.xlam) :

Function MYVALUE(x as integer)
    MYVALUE = 123
End Eunction

Function MYARRAY(x as integer)
    MYARRAY = Array(10,20,30)
End Eunction

Answer 1

the reason is that your vector actually is being displayed horizontally, if you create an array formula horizontally you will get your numbers. For you to have a vertical vector use the following:

Function MYARRAY(x As Integer)

    MYARRAY = Application.WorksheetFunction.Transpose(Array(10, 20, 30))

End Function

Answer 2

Array formulas need use like that

my VBA to split text in multi cells

Function EXPLODE_V(texte As String, delimiter As String)
    EXPLODE_V = Application.WorksheetFunction.Transpose(Split(texte, delimiter))
End Function
Function EXPLODE_H(texte As String, delimiter As String)
    EXPLODE_H = Split(texte, delimiter)
End Function

1. Select region C3:C7 this define the vector direction.
2. Press F2 to edit on the spot and type the following formula: =EXPLODE_V($B$3;" ")

3. Press CTRL+SHIFT+ENTER ( INSTEAD of usual ENTER ) - this will define an ARRAY formula and will result in {=EXPLODE_V($B$3;" ")} brackets around it (but do NOT type them manually!).

   

Answer 3

A cell won't display an array, you would have to translate that array to a range/array of cells on the sheet. Alternatively, you could try converting the array to a delimited string, which may work as long as you aren't using very large arrays.

Public Function MYARRAY(x As Integer)

Dim tmpArray() As Variant
Dim i As Long
Dim arrayString As String

tmpArray = Array(10, 20, 30)

For i = LBound(tmpArray) To UBound(tmpArray)
    If arrayString = vbNullString Then
        arrayString = tmpArray(i)
    Else:
        arrayString = arrayString & ", " & tmpArray(i)
    End If
Next

MYARRAY = "{" & arrayString & "}"

End Function