Type mismatch in VBA LinEst function if range too large

[RyanVM]

I've stumbled across a very strange problem while performing linea
regression analysis on data with a macro I'm writing.

I've included a link to a sample worksheet I created which illustrate
the problem. Basically, the LINEST function if used as a cell functio
is fine with pretty much any data range thrown at it. However, if yo
attempt to perform a LinEst within VBA, a range of more than 2730 dat
points causes the macro to stop with a type mismatch.

In the worksheet included, all that must be changed are the value i
MinRow and MaxRow. The macro included will attempt to perform a LinEs
calculation and write the parameters to cells along with writing manua
LINEST formulas to cells for Excel to compute. If the range is greate
than 2730 points (i.e. a range of 2-2731 is OK but not 2-2732), th
LINEST values in the cell will update and work fine, but the macro wil
stop and be unable to perform the LinEst calculation in the macro.

I've tested and confirmed this behavior in both Excel 97 and Exce
2000.

If anybody has a clue as to why that happens and (more importantly)
fix for it, I would be most grateful.

http://www.ryanvm.net/linesttest.zi

 

[Tom Ogilvy]

I suspect you are running into limitations in VBA.

http://support.microsoft.com/default.aspx?scid=kb;en-us;137921&Product=xlw
Error Message Appears If Array Contains More Than 2,730 Elements

http://support.microsoft.com/default.aspx?scid=kb;en-us;177991&Product=xlw
XL: Limitations of Passing Arrays to Excel Using Automation

Although the first is not specific to the versions you cite, a 2D array (2
* 2730 = 5460) approaches the 5461 limit spoken of for those versions. So I
suspect there is a relationship. If you are bound by a limitation, I
wouldn't see there being a work around (other than putting the formula in
the worksheet).

 

[RyanVM]

Sure enough, it works fine in Excel 2003. Time to see if I can convinc
my employer to spring for a newer version of Excel :

 

[hgrove]

RyanVM wrote...

Sure enough, it works fine in Excel 2003. Time to see if I can
convince my employer to spring for a newer version of Excel

Unless your workbooks are used by no one else in your company, you
employer would be very unwise to upgrade unless everyone using Exce
were upgraded to the same version

 

[RyanVM]

97 and 2003 use compatible file formats last time I checked. I've bee
able to bring stuff to and from work without issues (I use 2003 a
home) in the past.

But actually, the macro I'm writing would be very specific to on
computer, so it wouldn't be a huge deal