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