LINEST with an autopopulating array within VBA

Hi all,

essentially I am trying to populate an array within a VBA code and then regress the line using the LINEST function

Based on something I saw in one of these forums I tried the following

  Sub LinestTest()
Dim Y As Variant, X As Variant
 
ReDim Y(1 To 3, 1 To 1)
ReDim X(1 To 3, 1 To 1)
 


q = 4
For i = 1 To 3
    Y(i, 1) = q
    q = q + 1
Next


Z = 4
For i = 1 To 3
    X(i, 1) = Z
   Z = Z + 1
Next


 
C1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Y, X ^ [1,2,3,4,5,6]), 1)
 

 
End Sub

however I get a mismatch error can anyone explain what is wrong with the code and what I need to do to extract the regressed coefficients (1-7)

This forum tends to be pretty strict about putting code inside of code tags (see the forum rules link for instructions on how to use code tags).

This

X ^ [1,2,3,4,5,6]

is something VBA does not know how to do. As part of an array function, Excel knows how to take an array, raise each element to a power, and output an array based on the two input arrays. VBA, on the other hand, does not know how to do this.

In order to do this in VBA, you need to build the entire X input matrix as a 2 dimensional array:

x(1,1)=z(1)^1,x(1,2)=z(1)^2,x(1,3)=z(1)^3,...
x(2,1)=z(2)^1,x(2,2)=z(2)^2,...
...

Another error to note while you are testing -- you can't fit a 6th order polynomial with only 3 data points. You will need at least 7 data points to fit a 6th order polynomial.

I'm not sure how best to explain this. Let's go back and review the LINEST function to understand how it works http://office.microsoft.com/en-us/ma...823.aspx?CTT=1

The equation for the line is ... y = m1x1 + m2x2 + ... + b (if there are multiple ranges of x values)

When we regress a polynomial like you are talking about, we are simply taking advantage of LINEST's ability to handle multiple independent variables. LINEST() thinks that each xi is an independent variable, we are simply creating a situation where each xi is a different function of our original x. What the A8:A48^{1,2,3,4,5,6} part of the function does is builds a matrix of values (one column for each power of x) that can be passed to the LINEST() function. (see attached sheet for an illustration).

VBA does not know how to build the matrix for the known_x's argument the way that Excel does -- it cannot work with the "range^{array}" format like Excel. So, before we can invoke the LINEST() function from VBA, we have to build the matrix for the known_x's argument. Once that matrix is built (for a 6th order polynomial, it should be an nx6 matrix), it can be passed to the LINEST() function from VBA just fine.

Using similar code to what you have:

dim y(1 to 10,1 to 1), x(1 to 10, 1 to 6)
for i=1 to 10
y(i,1)=3+i
x(i,1)=(3+i)^1
x(i,2)=(3+i)^2
...'note that this particular data set will form a straight line and may not yield a proper 6th order polynomial
next i
result=application. ... .linest(y,x,1)

You could use either of these constructs:

    With Application
        C = .LinEst(Y, .Power(X, [{1, 2, 3, 4, 5, 6}]))
        C = .LinEst(Y, .Power(X, Array(1, 2, 3, 4, 5, 6)))
    End With