Problem using linest in vba

Question

This question is about the LINEST function.

I need to be able to use LINEST in a VBA subroutine.

I am testing it in the sub shown below.

First some background, and then the question.

In a spreadsheet, LINEST can be called by the following statement

=LINEST(D5:D18,C5:C18^{1,2,3,4},1,1)

where LINEST(known_y's, known_x's[^list_of_exponents] , TRUE, TRUE)

the list of exponents   ^{1,2,3,4}  is used to perform a polynomial regression.

The function can also be called by the following statement.

=LINEST(yvalues,xvalues^COLUMNS($A:$C),1,1)

The function call works on the spreadsheet, but when using it in VBA, how do I add the list

of exponents?  I have tried several approaches

that are shown as comments below,  and none have worked.

Below is the current text in my subroutine

The test sheet has a series of numbers 1,2,3, etc in column C5:C18

and some test data with a positive slope in column D5:D18

so column C is the x variable, and column D is the y variable.

' Keith

Sub LINEST_in_VBA_Test_2a()

Dim output As Variant, x As Variant, y As Variant, z As Variant

'LINEST(known_y's, known_x's, TRUE, TRUE    for polynomials use.... =LINEST(D5:D18,C5:C18^{1,2,3,4},1,1)

'x = Range("C5:C18^{1,2,3,4}")              '<----Error if this is used

'x = Range("C5:C18") ^ [1,2]                  '<----Error if this is used

'x = (Range("C5:C18")) ^ [1,2]                '<----Error if this is used

'x = Range("C5:C18" ^ Range("K5:K8"))  '<----Error if this is used  -- K5:K8 contains 1, 2,3, and 4

'x = Range("C5:C18^Range(K5:K8)")       '<----Error if this is used  -- K5:K8 contains 1, 2,3, and 4

'x = "(C5:C18)^[1,2,3]"                           '<----Error if this is used

x = Range("C5:C18")                              '<---- no error here, but without the list of exponents, the result is simply the information about a simple linear regression.

y = Range("D5:D18")                              '<---- no error here

output = Application.LinEst(y, x, 1, 1)

'The following code puts the output onto the current sheet

For i = 1 To UBound(output, 1)

If i = 1 Then Cells(43 + i, 13).Value = "Slope & Intercept"

If i = 2 Then Cells(43 + i, 13).Value = "+ or - "

If i = 3 Then Cells(43 + i, 13).Value = "r squared & s(y)"

If i = 4 Then Cells(43 + i, 13).Value = "F & df"

If i = 5 Then Cells(43 + i, 13).Value = "regression & ss"

For j = 1 To UBound(output, 2)

Cells(42 + i + 1, 13 + j).Value = output(i, j)

Next j

Next i

End Sub

Answer 1

Try (copy and paste both the Sub and Function in a module and then run the Sub LINEST_in_VBA_Test_2a

'Credit: Function XArray is taken from Tushar Mehta's article http://www.tushar-mehta.com/publish_train/data_analysis/16.htm

Sub LINEST_in_VBA_Test_2a()

Dim output As Variant, x As Variant, y As Variant, z As Variant

Set x = Range("c5:C18")

Dim XArr()

XArr = XArray(x.Value, 4)

Set y = Range("D5:D18")

output = Application.LinEst(y, XArr, 1, 1)

   'The following code puts the output onto the current sheet

   For I = 1 To UBound(output, 1)

   If I = 1 Then Cells(43 + I, 13).Value = "Slope & Intercept"

   If I = 2 Then Cells(43 + I, 13).Value = "+ or - "

   If I = 3 Then Cells(43 + I, 13).Value = "r squared & s(y)"

   If I = 4 Then Cells(43 + I, 13).Value = "F & df"

   If I = 5 Then Cells(43 + I, 13).Value = "regression & ss"

   For J = 1 To UBound(output, 2)

        Cells(42 + I + 1, 13 + J).Value = output(I, J)

   Next J

   Next I

End Sub

Function XArray(InArr, PolyPower)

' taken from Tushar Mehta's article http://www.tushar-mehta.com/publish_train/data_analysis/16.htm

    Dim XArr()

    Dim I As Long, J As Long

    ReDim XArr(LBound(InArr) To UBound(InArr), 1 To PolyPower)

    For I = LBound(XArr, 1) To UBound(XArr, 1)

        XArr(I, 1) = InArr(I, LBound(InArr, 2))

        For J = 1 To PolyPower

            XArr(I, J) = XArr(I, 1) ^ J

            Next J

        Next I

    XArray = XArr

    End Function

Answer 2

It worked for me. Did you copy the following function below your macro;

Function XArray(InArr, PolyPower)

' taken from Tushar Mehta's article http://www.tushar-mehta.com/publish_train/data_analysis/16.htm

Dim XArr()

Dim I As Long, J As Long

ReDim XArr(LBound(InArr) To UBound(InArr), 1 To PolyPower)

For I = LBound(XArr, 1) To UBound(XArr, 1)

XArr(I, 1) = InArr(I, LBound(InArr, 2))

For J = 1 To PolyPower

XArr(I, J) = XArr(I, 1) ^ J

Next J

Next I

XArray = XArr

End Function

Answer 3

Thank you very much. 

I tested this , and looked at the code that was in the source for this data.

My code results in an error on the line..

XArr=XArray(x.Value,4)

I suspect that's because XArray is not defined anywhere.

I wonder what you found when you tested it.

Keith

Answer 4

Thank you,

I added  "Set" before the x and y statements and it works fine.  I've found that vba  typically works with and without  "set".

The problem is now to incorporate the language that tells the function to do a polynomial regression.

In the spreadsheet function call that looks like this...

^{1,2,3,4}

but I haven't been able to figure out a vba equivalent.

Keith

Answer 5

Try to replace the following lines

x = Range("C5:C18")                           

y = Range("D5:D18")       

with (add Set before x & y)

Set x = Range("C5:C18")                           

Set y = Range("D5:D18")              

Let me know how it goes... with 10 in each cell in C5:C18 and 5 in each cell in D5:D18 I got the following output

Slope & Intercept	0	5
+ or -	0	0
r squared & s(y)	1	0
F & df	#NUM!	13
regression & ss	0	0