title: Passing arrays from an Excel VBA function to a spreadsheet
body: <p>I've passed arrays back and forth from spreadsheets to VBA functions many times.  I recently "upgraded" to Excel 365, and now I can't get it to work.  For example:</p>
  
  <pre><code>Public Function test(x As Range)
    Dim y()
    ReDim y(3)
    y(1) = 1
    y(2) = 2
    y(3) = 3
    test = y
  End Function
  </code></pre>
  
  <p>Then I highlight three cells, for example, b1:b3, and in the top cell I enter =test(a1:a2) and hit shift-enter.  This fills the range with an array formula that is supposed to receive y() from the test function. </p>
  
  <p>However, the cells that reference the function are all zeroes.  I put in debugging lines and I can tell the function is running as intended.  It's just not passing the array to the spreadsheet.  </p>
  
  <p>What's up with that? Has anyone else had this experience?</p>
  
  <hr>
  
  <p>@RDHS, @tim-williams  and @garys-student - thank you for your spot-on answers.  And Gary's student - thanks for the incredibly quick response.  I'd vote everyone up but I can't 'cuz i'm noob.  </p>
  
  <p>But... for completeness' sake -- Your answer raise another question (of a more theoretical type): I SHOULD BE able to coerce a one-dimensional array into a range column directly, and vice versa. </p>
  
  <p>Obviously it's easy enough to check the shape of the range and transform it accordingly (well, it's easy now that that you've shown me how!) But it's so sloppy: </p>
  
  <p>using the above example, instead of just writing 
    test = y </p>
  
  <p>I need to write:</p>
  
  <p>if x.rows.count=1 then 
    test = y<br>
  else
    test = worksheetfunction.transpose(y)
  end if</p>
  
  <p>I don't know about you but I'd take Door # 1 (test=y).  The other way is SOOOO sloppy.</p>
  
  <p>But MS is holding out on us - excel doesn't force you to do those gymnastics when using built-in spreadsheet array functions like index, match, etc... Index(C1:C10,3) and index(a3:k3,3) both return the value in C3, which is the third ITEM in each ARRAY.  Index is smart enough to figure out which is the third item.  Surely if you can do it on a worksheet, there must be a way to do it in VBA??</p>
  
  <p>My favorite Comp. Sci. professor - one of the founders of the field of computer science - used to say, "A programming language is low level when its programs require attention to the irrelevant."  </p>
  
  <p>He actually made a lot of insightful observations, which he distributed over the ARPANET, making him one of the world's first bloggers (Google Alan Perlis). For twenty years, every programmer had a list of Perlisisms taped above his VT100 -- like:</p>
  
  <ul>
  <li><p>"In computing, turning the obvious into the useful is a living definition of the word 'frustration'";</p></li>
  <li><p>"One man's constant is another man's variable"; </p></li>
  <li><p>"Fools ignore complexity. Pragmatists suffer it. Some can avoid it. Geniuses remove it."  </p></li>
  </ul>
  
  <p>I bring him up because the desire to produce "clean" code goes way back to the first coders on the first computers.  And I was very fond of him.</p>