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

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
– Vico, Commented Oct 24, 2017 at 16:00

Gary's Student · Accepted Answer · 2017-10-24 17:59:11Z

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

edited Oct 24, 2017 at 17:59

answered Oct 24, 2017 at 16:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user4039065 Over a year ago

The range of cells (2 rows by 2 columns) also gets 'locked' so that any changes must be made to all the cells in the range; you cannot make a change to a single cell.

Gary's Student Over a year ago

@Jeeped See my EDIT#1

siz Over a year ago

I don't know the number of rows the function returns. Number of columns is predefined. How do I set row range dynamic?

Gary's Student Over a year ago

@siz You would use a Sub rather than a Function. The sub can make its own decisions about where its output goes.

siz Over a year ago

Ok, how do i call a sub from excel formula?

|

jlmmg · Accepted Answer · 2022-07-27 16:36:42Z

0

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

edited Jul 27, 2022 at 16:36

answered Aug 11, 2020 at 14:16

jlmmg

11 bronze badge

Collectives™ on Stack Overflow

excel vba function return array and paste in worksheet formula

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related