In VBA, how to return an array / or write to cells using a function?

Question

Using this very simple function:

Function WriteArray() as Variant
 Dim array(0 To 2)
 array(0) = "A"
 array(1) = "B"
 array(2) = "C"
 WriteArray = array
End Function

I was expecting to see in result the whole array in my Excel spreadsheet, but that's not the case: I get only the first string. I know there is trick to show the whole array in the spreadsheet (by selecting a range of cells with the formula + F2 + CTRL+SHIFT+ENTER), but I would prefer VBA to handle it all.

I also tried to use the Application.Caller variable to write directly in the "Caller" range but the code breaks.

Thanks a lot for your help!

EDIT: Here is another code I tried to use:

Function WriteArray() As Variant
     Dim arr(0 To 2)
     arr(0) = "A"
     arr(1) = "B"
     arr(2) = "C"
     WriteArray = arr
     Dim StartRow, i As Integer
     For i = 0 To UBound(arr)
        Range("A" & i).Value = arr(i)
     Next
End Function

It breaks at the line "Range("A" & i).Value = arr(i)". Is my Excel broken?!

Answer 1

The following code writes the array to a range of cells beautifully:

Function WriteArray() As Variant
    Dim AbcList(0 To 2) as Variant
    AbcList(0) = "A"
    AbcList(1) = "B"
    AbcList(2) = "C"
    WriteArray = AbcList
End Function

Function WriteArrayToSpreadsheet()
    Dim MyArray As Variant
    MyArray = WriteArray()

    Dim StartRow, i As Integer
    StartRow = 1
    For i = 0 To UBound(MyArray)
        Range("A" & i + StartRow).Value = MyArray(i)
    Next
End Function

That being said, I'd like to see the part of the code where you're actually trying to get it onto the spreadsheet, not where you build the array. Then I can help you out!

Answer 2

You are not allowed to write to non-caller cells directly from a worksheet function in Excel.

If you want to use an array function (using the Shift-Ctrl-Enter) you need to change your code to:

Function WriteArray() As Variant
    Dim arr(0 To 2, 0 To 1)
    arr(0, 0) = "A"
    arr(1, 0) = "B"
    arr(2, 0) = "C"
    WriteArray = arr
End Function

If you want to write outside of the calling cells you would need to implement some form of callback which would use automation to write to the other cells. This is way more complicated and much more likely to break!