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?!

Solution 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!

Solution 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!

Solution 3

The secret is to define a two-dimensional array. The two dimensions of the array is simply the range which needs to be defined for a dataset. The first array dimension is the row offset and the second dimension is the column offset.

In you example the second dimension is just not "used":

 Sub Ente()

   Dim myArray(0 To 3, 0) As String
   myArray(0, 0) = "A"
   myArray(1, 0) = "B"
   myArray(2, 0) = "C"

   Range("B7:B" & UBound(myArray) + 6) = myArray

End Sub

So, no loops necessary! Simple and fast.

Comments

[Admin]

In fact this is not working, the VBA code breaks at "Range("A" & i + StartRow).Value = MyArray(i)"... that's my problem also. Is this code works on your Excel!? I use Excel 2003.

[Eric]

It worked perfectly in Excel 2003. Change every "array" in your beginning code to another variable name that's NOT a reserved word, like AbcList or something of the sort.

[Admin]

Still, it breaks... I use exactly this code: 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 At "Range("A" & i).Value = arr(i)", it just exits the code, even without throwing any errors... doesn't make sense to me :(

[Admin]

I edited my question with this code, as it doesn't appear correctly here :)

[Eric]

Nope, you need to set StartRow and add it to i. Your code is trying to write to A0, which doesn't exist and will throw an error.