Populating Excel multiple cells with data from Add-in user defined function (UDF)

Question

I am working on an Add-in for Excel that needs to populate one or more cells in the worksheet with the data from server. I did following:

1. Created an Add-in (xlam) and created the user defined function: GetMyData()
2. I am calling this function from a simple Excel Worksheet. In A1 cell, I entered the formula =GetMyData()
3. My server returns JSON Array. I am able to parse the JSON and now trying to populate the A1 and the below rows with values I got from the server. (in this case, I have 20 values, so I want to populate A1:A20 cells).

The issue is that according to Microsoft KB, a user defined function is able to change the value of the active cell only. https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel I also tried to run the code below that changes only one cell to a hardcoded value:

ActiveWorkbook.Sheets("DataSheet").Cells(1, 1).Value = '12312'

Still - getting exception.

Could any one assist with this issue?

Answer 1

Here is a simple example:

Public Function cerial(d As Date)
    Dim bry(1 To 20, 1 To 1) As String
    For i = 1 To 20
        bry(i, 1) = CStr(d) & "_" & i
    Next i
    cerial = bry
End Function

It creates an array of 20 items.

There is a tiny trick to using it in the worksheet.

Select, say, E1 through E20. Then click in the formula bar and enter the array formula:

=cerial(TODAY())

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done properly, the formula will appear enclosed in brackets in the Formula Bar.

NOTE:

In this example, the internal array bry() is two dimensional.

Answer 2

Have the function return a vertical array.

For Example:

Function GetMyData() As Variant()
Dim test() As Variant
test = Array(1, 2, 3, 4)
GetMyData = Application.Transpose(test)

End Function

Then highlight all the cells you would ever need with the top as the active cell.

Then put this formula in the formula bar:

=GetMyData()

And hit Ctrl-Shift-Enter instead of Enter to array enter the formula.

If you choose more than the array returns you will get errors.

---

Or you can, using the Array return, use this to deal with error and enter normally, but it runs the function for each time it is put in a cell.

Put this in the first cell:

=IFERROR(INDEX(GetMyData(),ROW(1:1)),"")

Then copy/drag down till you get blanks