excel vba function return array and paste in worksheet formula

1
votes

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
2
arraysexcelvbafunctionformula
I believe that if you return an array, you have to select multiple cells and press shift-enter in order to get all those values. You can use index() to get the specific values within the array such as: =INDEX(A1:A4,3) – Vico
Read this: cpearson.com/excel/arrayformulas.aspx – Tim Williams

2 Answers

1
votes

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:

enter image description here

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
0
votes

Use join to convert array to string, check Cell properties>Wrap text. then you can populate array to single cell.

Value(0) = "somedata"
value(1) = "somedata"
GetData = Join(value, Chr(10))

P/s: Idk how to join 2 dimensions array