Array size limit while using worksheet functions?

Question

I am trying to use LinEst to process very large size of data. And I found that once the array size exceeds 65536 (16-bit size pretty much), it gives an error message like "Type Mismatch". Is there any way that I can go around this problem and use a large size array in my macro code?

BTW, I am using Office2000, though I tried it on Office2013 too. It is the same for the both versions.

Thanks!

PS:

Here is the code that I used for testing

    Dim xx(0 To 65536, 0 To 1) As Double

    Dim yy(0 To 65536, 0 To 0) As Double

    Dim regtest As Variant

    regtest = WorksheetFunction.LinEst(yy, xx, True, True)

Answer 1

Xiaolei Lui wrote:

I am trying to use LinEst to process very large size of data. And I found that once the array size exceeds 65536 (16-bit size pretty much), it gives an error message like "Type Mismatch". Is there any way that I can go around this problem and use a large size array in my macro code?

I concur:  it looks like a limitation of WorksheetFunction.LinEst.  It works for arrays up to 65536, but not larger.  I am using Excel 2010.

But the limitation is in the VBA WorksheetFunction implementation.  Excel LINEST does not seem to have any such limitation, at least not in Excel 2010.

(I do not know about Excel 2000.)

So you might redesign your implementation so you can use Excel LINEST instead of WorksheetFunction.LinEst.

After writing your Y and X values into a temporary worksheet, use Evaluate("LINEST(...)") in VBA.

Potential advantage:  if you are using LINEST for nonlinear trendlines, you do not have to calculate the x-value(s) yourself.