excel vba function return array and paste in worksheet formula

Question

Is it possible for an vba function to return array values and paste in the sheet with excel formula?

For example, I want to write a formula in excel cell say A1 like =GetData() and press enter. It returns 4 metrics with A1:A4,B1:B4 cells filled in.

I tried Variant and Collection as return types. The values are there but it only populates the first cell.

Function GetData(Input1 As Range) As Variant       
    Dim value() As Variant
    value(1, 1) = "somevalue"
    value(1, 2) = "somevalue"
    ............
    value(2, 2) = "somevalue"

    GetData = value
End Function

Answer 1

With this code:

Function GetData() As Variant
    Dim value(1 To 2, 1 To 2) As Variant
    value(1, 1) = "1;1"
    value(1, 2) = "1;2"
    value(2, 1) = "2;1"
    value(2, 2) = "somevalue"
    GetData = value
End Function

Select cells D1 through E2; then click in the Formula Bar; then enter:

=getdata()

as an array formula:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

EDIT#1:

As Jeeped pointed out, once the formulas have been array-entered in this fashion, a single cell in the array cannot be changed by itself. So:

Range("D1").Clear

will fail. You would need:

Sub poiuyt()
    Dim r As Range, r2 As Range

    Set r = Range("D1")
    Set r2 = r.CurrentArray
    r2.Clear
End Sub