Perform multipli regression with VBA

[Prack]

Hi, I'm trying to obtain one coefficient and its error term out of a multiple regression (linest) starting from a single series of data and using only VBA. This is the code I've written and that doesn't work. I believe my problem is determining the range data on which the regression is to be performed. Any help is more than welcome!

Function MultipleRegression(Series As Range)
n = Series.Rows.Count
Dim y(1 To n), x(1 To n, 2) As Variant
For i = 1 To n
y(i) = Series(i + 1) - Series(i)
x(i, 1) = Series(i + 2) - Series(i + 1)
x(i, 2) = Series(i + 3) - Series(i + 2)
Next i
Set y = Range(y(1), y(n))
Set x = Union(Range(x(1, 1), x(n, 1)), Range(x(1, 2), x(n, 2)))
Coefficient = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 1, 1)
CoefficientError = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(y, x, True, True), 2, 1)
Result = Coefficient / CoefficientError
MultipleRegression = Result
End Function

[TMS]

Welcome to the forum.

Please take a few moments to read the rules of the forum and add Code tags to your code sample.

To save you some time, it is worth noting that there is a Series object so "Series" is probably a reserved word and VBA may take exception to you using it as a range.

Also, you can't use a variable in a Dim statement; you'll need to resize the array as part of the code.

If you still need assistance, please attach a sample workbook.

Regards