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]

You can use Linest with an array in VBA - are you sure that your arrays are the same sizes and that they contain suitable values? What is the actual problem you are having?
To make debugging easier, you may want to assign the results of each step to variables so you can see the evaluation steps.

 

[Sherlock]

I have been posting inpv() and inps() out to excel just to check. The numbers in these and the number of numbers match those in range(Vc) and range(SPd) exactly so I think the dimensions and contents of the arrays inpv() and inps() are correct?

I've even changed these now so that I'm picking up cells into the array as below (a differently named array is output to cells H7,I7, etc)

For p = 1 To z
ReDim Preserve inpv(z)
ReDim Preserve inps(z)
inpv(p) = Range("H7").Offset(p, 0).Value
inps(p) = Range("I7").Offset(p, 0).Value
Next p

Thanks!

 

[RoryA]

Does your b version work if you use:

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

The loop you posted above looks like you would end up with empty values in the array (since your first offset is 1 row, not 0) - that would cause an error.

 

[Sherlock]

Thanks for getting back to me!
Adding the .value to b still works. For the loop the first cells with values in are H8, I8 so I think that should be fine.

 

[RoryA]

OK, so what is the actual error you are getting? Can you tell me what values you have in the 2 arrays when it fails, as well as which version of Excel you are using?

 

[Sherlock]

The error I get is Run-time error '1004'- unable to get the LinEst property of the Worksheet Function class

I have engineered it now so that I only have 5 items in each array (there would typically be a large number and this varies each time as inps calculated using MC) . Column on the left is inpv, comment on the right is inps

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right width=64 height=17 x:num="0.57179454404335972">0.571795</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" align=right width=64 x:num="1.6185014801398865">1.618501</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right height=17 x:num="1.2329484537228628">1.232948</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" align=right x:num="0.66471980203146408">0.66472</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right height=17 x:num="1.1271185408011328">1.127119</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" align=right x:num="1.4319400783403828">1.43194</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right height=17 x:num="0.42631517727789991">0.426315</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" align=right x:num="1.6944613490206153">1.694461</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" align=right height=17 x:num>0</TD><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: white" align=right x:num="1.9350612253998154">1.935061</TD></TR></TBODY></TABLE>

 

[pgc01]

Hi Sherlock

I answered a similar question some time ago here:

http://www.mrexcel.com/forum/showthread.php?p=1616944#post1616944

The first posts in the thread use ranges but post #7 has an example unsing only vba arrays, 2 independent variables.

HTH

 

[RoryA]

I think the problem is the shape of your arrays. This works for me:

   Dim arr1(1 To 5), arr2(1 To 5)
   Dim varDataP, varDataL
   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 = Application.LinEst(arr1, Application.Transpose(varDataP))

 

[Sherlock]

Interesting, I have done the below:

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

which is quite similar to yours but does not work -I have added the ,1 on the end as c is the coefficient of the x^2 term, for a, b I would need to add ,1,2 or ,1,3. Maybe I need to add this part differently?