LinEst with more than 65536 rows

[JB3717]

Hi guys.

I've been searching through the forums, but I can't seem to find anything on the subject.

I'm using the worksheet funtion "LinEst" in VBA to make regression in a monte carlo simulation. It works just fine with a low number of simulations, but I would like to make 100,000 simulations and then I get a type mismatch error message from the worksheetfunction.linest. I figured out through trial and error that the error occurs, when I try to do more than 65536 simulations. Doing a quick search on google I found out that 65536 was the maximum number of rows in previous versions of Excel. I use 2007, but it seems that the LinEst function hasn't been updated to handle the larger number of rows in Excel 2007. Does anybody know how I can resolve this issue or have any other piece of advice?

Regards

JB3717

 

[pgc01]

Hi JB

You did not say it but I guess you are working with arrays. In this case you are right, the worksheetfunction Linest() seems not to accept arrays with more than 64k elements.

A possible workaround is to copy the array into a range and use the worksheetfunction Linest() referring to the range.

I had no problem with:

Dim vLE As Variant
 
vLE = Application.WorksheetFunction.LinEst(Range("A1:A100000"))