I am having some difficulty using an array in the worksheet function "LinEst" which is embedded in a user-defined function. I am trying to create a polynomial regression function that takes the desired degree as an input along with the known x and y values. I have confirmed that my arrays are accurate by placing them into a spreadsheet, but when I use the arrays in the Linest formula I get the #VALUE error.
I am unfamiliar with how worksheet functions accept arrays in VB.
Thanks in advance for the suggestions and help.
I am unfamiliar with how worksheet functions accept arrays in VB.
Code:
Function Polynomial_Regression(Known_Ys As Range, Known_Xs As Range, Degree As Single)
Dim strDegreeArray() As String
Dim strXraisedArray() As String 'Multidimensional Array to hold X range and X raised to degree(y) Power
Dim strTransposeXraised As Variant 'Variable to hold Transposed Xraised Array
Dim strCoefficients() As String 'Array to hold coefficient to use in polynomial function
Dim a, b, c As Integer 'Counter Variables
Dim d, e, f As Single 'More Counter Variable
Dim i, j, k As Object 'Counter variables for for loops
ReDim strDegreeArray(0 To Degree - 1) As String
a = 0
For i = 0 To Degree - 1
strDegreeArray(i) = a + 1
a = a + 1
Next i
'create the multidimensional X raised to y power array
ReDim strXraisedArray(0 To Degree - 1, 0 To Known_Xs.Count - 1) As String
'First set Known_Xs into Array
a = 0
For Each i In Known_Xs
strXraisedArray(0, a) = i
a = a + 1
Next i
'Second populate array with calculation outputs
a = 0
b = 1
For i = 0 To Known_Xs.Count - 1
For j = 0 To Degree - 2
strXraisedArray(b, a) = strXraisedArray(0, a) ^ (b + 1)
b = b + 1
Next j
a = a + 1
b = 1
Next i
'Transpose Array because by default arrays are in the 1row x multi column arrangement
strTransposeXraised = Application.WorksheetFunction.Transpose(strXraisedArray)
'ReDim Preserve strTransposeXraised(0 To UBound(strTransposeXraised), 0 To UBound(strTransposeXraised)) As Variant
'ReDim strCoefficients(0 To Degree) As String
Polynomial_Regression = Application.WorksheetFunction.LinEst(Known_Ys, strTransposeXraised)
End Function
Thanks in advance for the suggestions and help.