Array function in Excel VBA

Question

I have a function in VBA that generates an array of strings. It works fine when called from another VBA function, but not when called from the worksheet.

Here's how it should be used:

• select A1:A3
• write in the formula bar =Test(), then hit Ctrl-Shift-Enter to make it an array function
• A1 should contain A, A2 should contain B, and A3 should contain C

When I actually try this, it puts A in all three cells of the array. How can I get the data returned by Test into the different cells of the array?

---

For those who'd like to see it, here's the code of the function. Keep in mind, the function works fine when called from other functions.

Function Test() As String()
    Dim a(1 To 3) As String
    a(1) = "A"
    a(2) = "B"
    a(3) = "C"
    Test = a
End Function

Answer 1

You function works fine if you transpose the data. To do this, you need Variant() instead of String():

Function Test() As Variant()
    Dim a(1 To 3) As Variant
    a(1) = "A"
    a(2) = "B"
    a(3) = "C"
    Test = Application.Transpose(a)
End Function

Answer 2

You might want to check Chip Pearson's advice on returning an array from a UDF.

This is my first response here. I hope this is not inappropriate.

Answer 3

I find it simplest to always work with 2-dimensional arrays, then you don't need transpose etc

Function Test() As String()
    Dim a(1 To 3, 1) As String
    a(1, 1) = "A"
    a(2, 1) = "B"
    a(3, 1) = "C"
    Test = a
End Function