CODEWITHSUNDEEP
vbaexcelregressionexcel-2013
SilvaAger
SilvaAger

Reputation: 13

Linest Polynomial Regression in Excel's VBA

I am having issues finding information on using Linest in Excel's VBA in a subroutine. I need to return the polynomial coefficients (third or fourth order) for a column of x values (Column A beginning in Row 17) and a column of y values (Column G beginning in Row 17).

The code I am currently using is shown below, and it works.

Dim X
X = Application.Evaluate("=linest(g17:g61,A17:A61^{1,2,3})")
Cells(3, 8) = X(1)
Cells(4, 8) = X(2)
Cells(5, 8) = X(3)
Cells(6, 8) = X(4)

Essentially, I need to be able to make this subroutine work on any length of data. Data will always begin to be entered in row 17. To do this, I have been trying to use code similar to that shown below. 

Range(Cells(i, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)), Range(Cells(i, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))

However, when I use similar ranges to those above instead of G17:G61 I get type mismatch errors. I don't understand why a range of cells is different from a range of cells in the format G17:G61. I've also tried using the simple Range(Cells(17,1),Cells(61,1)) instead of A17:A61.

I have also tried using "G" & 17:"G" & 61 instead of G:17:G61 as I have seen some do while browsing, but that doesn't seem to fix my issues either.

Is there another function that might be better?

Please help!

Upvotes: 0

Views: 6024

Answers (1)

GodLovesATrier
GodLovesATrier

Reputation: 71

The first comment on your question puts you on the right track, Application.Evaluate takes a string (you have to build the reference to the range in that string). The second method using Range and Cells creates an object reference to the Range, if you want to use object references then instead of evaluating a string you would pass the references to Application.WorksheetFunction.LinEst.

Here's an example using the cell references in your original question. 

Public Sub TestLinest()

Dim x
Dim i as long
Dim evalString As String
Dim sheetDisplayName As String
Dim polyOrder as string

' this is whatever the name of your sheet containing data:
sheetDisplayName = "Sheet1"
' this gets the index of the last cell in the data range
i = Range(sheetDisplayName & "!A17").End(xlDown).Row
' Obviously change this depending on how many polynomial terms you need
polyOrder = "{1,2,3}"

evalString = "=linest(" & sheetDisplayName & "!E6:E" & i & ", " & sheetDisplayName & "!D6:D" & i & "^" & polyOrder & ")"
x = Application.Evaluate(evalString)

Cells(3, 8) = x(1)
Cells(4, 8) = x(2)
Cells(5, 8) = x(3)
Cells(6, 8) = x(4) 

End Sub

You can see there are a couple of magic variables to fill in, in terms of ranges, sheet names and polynomial order. Perhaps this routine could be expanded to pass those as arguments to a function and return an array of coefficients?

At this point it might be worth mentioning that you could achieve all this without using VBA, which would mean that the spreadsheet logic remains more visible to you and/or your users. Here are some example functions to enable you to do all this on a sheet, here's the gist of it:

Get the total number of filled in cells (lets say this is in A1)

=COUNT(A17:A10000)

Enter this as an array formula:

=LINEST(INDIRECT("G17:G"&(A1+16)), INDIRECT("A17:A"&(A1+16))^{1,2,3})

The 16 and {1,2,3} in this formula can obviously be changed to suit your situation.

Upvotes: 1

Related Questions