CODEWITHSUNDEEP
arraysexcelvbafunctionformula
siz
siz

Reputation: 55

excel vba function return array and paste in worksheet formula

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

Upvotes: 1

Views: 4751

Answers (2)

jlmmg
jlmmg

Reputation: 1

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))

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Related Questions