LinEst in Vba - not using Excel ranges

[Sherlock]

Hi, I am calculating variables in Vba and want to find their co=effiicients in the for y=a +bx +cx^2. This works great if I output my variables to excel and then pick up this range however as I am generating the variables in vba and have an array of variables I would rather not have to keep spitting them out to excel then picking them up again. The three important lines in my code are:

c(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(inpv, Application.Power(inps, Array(1, 2))), 1)

b(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 2)

a(nFix - j) = WorksheetFunction.Index(WorksheetFunction.LinEst(Range(Vc), Application.Power(Range(SPd), Array(1, 2))), 1, 3)

As you can see for c I am referring to inpv and inps these are arrays. I have tried using inpv(), etc but no luck

b and a work great, here I am reffering to ranges Vc and SPd. As I am going through this a lot of times it seems rather inelegant not to be able to do the whole thing in vba - i would rather not use the dump to spreadsheet then pick up again approach!

Thanks for your help!!

 

[RoryA]

Are you still getting the same error? This worked for me:

   Dim arr1(1 To 5), arr2(1 To 5)
   Dim varDataP, varDataL, dataout
   arr1(1) = 0.571795
   arr1(2) = 1.232948
   arr1(3) = 1.127119
   arr1(4) = 0.426315
   arr1(5) = 0
   arr2(1) = 1.618501
   arr2(2) = 0.66472
   arr2(3) = 1.43194
   arr2(4) = 1.694461
   arr2(5) = 1.935061
   varDataP = Application.Power(Application.Transpose(arr2), Array(1, 2))
   varDataL = WorksheetFunction.Index(Application.WorksheetFunction.LinEst(arr1, Application.Transpose(varDataP)), 1)

 

[Sherlock]

Copying and pasting your code into a new sub works fine so I'm thinking there must be something odd going on with the dimensioning of my arrays. I'll have a further look through.

Thanks v much for your help!