title: excel vba function return array and paste in worksheet formula
body: <p>Is it possible for an vba function to return array values and paste in the sheet with excel formula?</p>
  
  <p>For example, I want to write a formula in excel cell say A1 like <code>=GetData()</code> and press enter. It returns 4 metrics with A1:A4,B1:B4 cells filled in.</p>
  
  <p>I tried Variant and Collection as return types. The values are there but it only populates the first cell.</p>
  
  <pre><code>Function GetData(Input1 As Range) As Variant       
      Dim value() As Variant
      value(1, 1) = "somevalue"
      value(1, 2) = "somevalue"
      ............
      value(2, 2) = "somevalue"
  
      GetData = value
  End Function
  </code></pre>