In addition to Jerry's adivce, Arrays passed to Linest appear to need to be
1 based, not zero based.
Here is a working example that returns the coefficients for a 6th degree
polynomial.
Sub abc()
Dim v2 As Variant
Dim v() As Double
Dim r() As Double
Dim v1 As Variant
Dim i As Long, j As Long
ReDim r(0 To 1, 0 To 6)
v2 = Array(5, 15, 89, 851, 4677, _
17615, 52025, 129939, 287141, _
577967, 1080825)
ReDim v(1 To UBound(v2) - LBound(v2) + 1, 1 To 1)
j = LBound(v2)
' make v a 1-based 2-D array
' it will be passed as Date
For i = 1 To UBound(v)
v(i, 1) = v2(j)
j = j + 1
Next
v1 = DoLinest(v, r)
Debug.Print v1
For i = LBound(r, 2) To UBound(r, 2)
Debug.Print i, Application.Round(r(LBound(r), i), 0)
Next
End Sub
Private Function DoLinest(Data() As Double, ByRef Result() As Double) As
Long
'On Error GoTo FunctionError
Dim wsf As WorksheetFunction
Set wsf = Application.WorksheetFunction
' Depending on the number of samples passed to the function in DATA(),
' number the x values from 0 to N.
Dim intXterms() As Long
Dim v As Variant, r1 As Long
Dim v1 As Variant, c1 As Long
Dim i As Long, j As Long, k As Long
ReDim intXterms(0 To UBound(Data))
Dim intCounter As Integer
For intCounter = 0 To UBound(Data)
intXterms(intCounter) = intCounter
Next intCounter
' The exponents are determined by the second part of the array, what
' would be columns in the worksheet. I am trying to get a sixth order,
' so this array would be {1,2,3,4,5,6}
' NOTE: For clarity, I should have labeled the intCoeff variable as
intExponents
intCounter = UBound(Result, 2) - LBound(Result, 2)
ReDim v1(LBound(Data) To UBound(Data), _
1 To intCounter)
k = 0
For i = LBound(Data) To UBound(Data)
For j = 1 To intCounter
v1(i, j) = (i - 1) ^ j
' Debug.Print " V1(" & i & "," & j & ")=" & v1(i, j);
Next
' Debug.Print
Next
' R1 = UBound(v1, 1) - LBound(v1, 1) + 1
' c1 = UBound(v1, 2) - LBound(v1, 2) + 1
' Worksheets("Sheet2").Range("A1").Resize(R1, c1).Value = v1
v = Application.LinEst(Data, v1, , True)
If IsError(v) Then
MsgBox "Bad data"
Exit Function
End If
j = LBound(v, 2)
For i = LBound(Result, 2) To UBound(Result, 2)
Result(LBound(Result, 1), i) = v(LBound(v, 1), j)
j = j + 1
Next
DoLinest = 0
Exit Function
FunctionError:
Debug.Print Err.Number
Debug.Print Err.Description
DoLinest = -1007
End Function
--
Regards,
Tom Ogilvy
Here is a working example.
"Jerry W. Lewis" wrote:
For starters, VBA does not support array arithmatic. You will need to
construct the appropriate X matrix instead of trying to compute it on the fly
with
intXterms ^ intCoeff
Jerry
"jnewby72" wrote:
I am trying to use the linest function to find a sixth order polynomial
for a data set of 10 samples in VBA. When I run the code below, I get
an error number 16, "Expression too complex". Any ideas about why the
expression is too complex for VBA, but not too complex for a
worksheet?
Code:
--------------------
Private Function DoLinest(Data() As Double, ByRef Result() As Double) As Long
On Error GoTo FunctionError
Dim wsf As WorksheetFunction
Set wsf = Application.WorksheetFunction
' Depending on the number of samples passed to the function in DATA(),
' number the x values from 0 to N.
Dim intXterms() As Integer
ReDim intXterms(0 To UBound(Data)) As Integer
Dim intCounter As Integer
For intCounter = 0 To UBound(Data)
intXterms(intCounter) = intCounter
Next intCounter
' The exponents are determined by the second part of the array, what
' would be columns in the worksheet. I am trying to get a sixth order,
' so this array would be {1,2,3,4,5,6}
' NOTE: For clarity, I should have labeled the intCoeff variable as intExponents
Dim intCoeff() As Integer
ReDim intCoeff(0 To UBound(Result, 2) - 1) As Integer
For intCounter = 1 To UBound(Result, 2) ' To 1 Step -1
intCoeff(intCounter - 1) = intCounter
Next intCounter
Result = wsf.LinEst(Data, intXterms ^ intCoeff, , True)
DoLinest = 0
Exit Function
FunctionError:
Debug.Print err.number
Debug.Print err.Description
DoLinest = -1007
End Function
--------------------
Thanks in advance.
--
jnewby72
------------------------------------------------------------------------
jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750
View this thread: http://www.excelforum.com/showthread...hreadid=525828