using linest excel function from msaccess with multidimensional ar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jobxyz - 25 Dec 2005 17:33 GMT

Hi

I am trying to call the excel function linest from VB using automation.

the call
Slope1 =
Excel.WorksheetFunction.Index(Excel.WorksheetFunction.LinEst(y, x),1)

works fine as long as x is a one dimensional array.

But I need to do regression analysis for a multidimensional array (e.g.
I have x1,x2,x3,...,xn independent variables in the regression.)

so when I define x as say
dim x (10,2) as double

I get an error messages
"unable to get the linest property of the worksheet function class"

anyone know how to use linest for a multidimensioanl array ?

Thanks

Reply to this Message

TC - 25 Dec 2005 17:38 GMT

I suggest you ask that in an Excel VBA group. It's an Excel function,
not an Access one.

HTH,
TC

Reply to this Message

jobxyz - 26 Dec 2005 05:57 GMT

Thanks for responding

In excel there is no problem - you simply use a two dimensional table spread
over rows and columns and the function picks it up. The problem arrises when
I try to use the function from access and deliver the 2D table as an array
from VB

> I suggest you ask that in an Excel VBA group. It's an Excel function,
> not an Access one.
>
> HTH,
> TC

Reply to this Message

TC - 26 Dec 2005 06:27 GMT

You're comparing apples with oranges - using LineEst with a worksheet
range, versus, using LinEst with a 2d array. Your problem is how to use
LinEst with a 2d array. You'll be far more likely to get help with that
in an Excel VBA group. Most of the folks in /this/ group, including me,
have probably never heard of the LinEst Excel function - have no clue
how to use it with a worksheet range /or/ a 2d array - and are probably
not willing to research the question, because this is an Access group -
not an Excel one.

Not being rude: just pointing out that you're in the wrong group to get
the best answer :-)

HTH,
TC

Reply to this Message