title: Array function in Excel VBA
body: <p>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.</p>
  
  <p>Here's how it should be used:</p>
  
  <ul>
  <li>select A1:A3</li>
  <li>write in the formula bar <code>=Test()</code>, then hit <code>Ctrl-Shift-Enter</code> to make it an array function</li>
  <li>A1 should contain <code>A</code>, A2 should contain <code>B</code>, and A3 should contain <code>C</code></li>
  </ul>
  
  <p>When I actually try this, it puts <code>A</code> in all three cells of the array.  How can I get the data returned by <code>Test</code> into the different cells of the array?</p>
  
  <hr>
  
  <p>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.</p>
  
  <pre><code>Function Test() As String()
      Dim a(1 To 3) As String
      a(1) = "A"
      a(2) = "B"
      a(3) = "C"
      Test = a
  End Function
  </code></pre>