Array function in Excel VBA

Question

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

Here's how to use it:

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

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

---

For those who would like to see this, here is the function code. Keep in mind that a function works great 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

The function works fine if you transfer data. For 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 out Chip Pearson's tip on returning an array from UDF .

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

Answer 3

I believe that the easiest way is to work with two-dimensional arrays, then you do not need to 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 

Answer 4

Just figured out the problem - the first dimension of an array going from VBA to Excel is horizontal , not vertical. To see the result of Test , put the array function =Test() in A1: C1 (not A1: A3).