UDF array function update issues

[PRyankid]

I have an array function that I have used to make give me the coefficiencts of the Least Squares polynomial regression. The function works fine to solve them, but when I do things like insert or delete rows or columns, the function breaks down and puts the first value of the array in all of the cells of the array. I try to then use Application.CalculateFull, but that does not work until I highlight one instance of the formula, press F2, then Ctrl-Shift-Enter to update the formula. Then I can use Application.CalculateFull to update the rest. Any advice?

Here is the Code:

Function PolyReg(xvals As Range, yvals As Range, RegOrder As Integer)
    ReDim xVect(0 To RegOrder * 2)
    ReDim yxVect(0 To RegOrder)
    ReDim LHMatrix(0 To RegOrder, 0 To RegOrder)
    Dim ListLen As Integer
    ListLen = Application.WorksheetFunction.Max(xvals.Rows.Count, xvals.Columns.Count)
    
    For i = 1 To ListLen
        xVect(0) = xVect(0) + 1
        For j = 1 To RegOrder * 2
            xVect(j) = xVect(j) + xvals(i) ^ j
        Next j
        yxVect(0) = yxVect(0) + yvals(i)
        For j = 1 To RegOrder
            yxVect(j) = yxVect(j) + yvals(i) * xvals(i) ^ j
        Next j
    Next i
    
    For i = 0 To RegOrder
        For j = 0 To RegOrder
            LHMatrix(i, j) = xVect(RegOrder - j + i)
        Next j
    Next i
    
    yxVect = Application.WorksheetFunction.Transpose(yxVect)
    LHMatrix = Application.WorksheetFunction.MInverse(LHMatrix)
    yxVect = Application.WorksheetFunction.MMult(LHMatrix, yxVect)
    If Selection.Rows.Count > 1 Then
        PolyReg = yxVect
    Else
        PolyReg = Application.WorksheetFunction.Transpose(yxVect)
    End If
End Function

 

[macleanb]

I think your issue is using the "selection.rows.count" in a function - doesnt sound like a good idea to me (e.g. its going to be very indeterminant) Find another way!