title: Excel VBA: Passing a calculation result array as an argument to a function
body: <p>I have a VBA function that accepts arrays, it works great if I pass it a reference such as <code>A1:A10</code>.  It does not accept a "formula" argument such as <code>A1:A10+1</code> or <code>A1:A10^2</code>, the resulting cells contain <code>#VALUE</code>.</p>
  
  <p>Many excel functions support this, such as linest(...) which i can write the following formula to get a forth order fit without having to manually list the four columns for the <code>X</code> argument.</p>
  
  <pre><code>     =LINEST( B1:B10, A1:A10^{1,2,3} )
  </code></pre>
  
  <p>My function declaration is:</p>
  
  <pre><code>     Function FFT(ByRef A) As Variant
  </code></pre>
  
  <p>I'm expecting Excel to evaluate the expression and pass the result into the function.  Is this not possible with Excel VBA? </p>