Thread: Passing Spreadsheet Ranges to VBA LINEST function

I am writing a VBA function which will take X and Y curve points and determine their polynomial trendline coefficients. I don't want to create an Excel chart to obtain the coefficients.

Eventually I'd like to let the user select a set of X and Y points on a spreadsheet, pass them to the VBA function and return different values back to the spreadsheet.

J-Walk & Associates webpage provides equations for calculating the trendline coefficients. Unforturnately, I can't get the function to work.

Here is the primary code which is causing the problem.

Sub TestHead()
    Dim Answer As Variant
    Answer = PumpHead(Worksheets("Sheet1").Range("A2:A8"), Worksheets("Sheet1").Range("B2:B8"), 800, 6)
End Sub

Public Function PumpHead(xFlow As Range, yTDH As Range, Flow As Double, Order As Integer) As Variant
    Dim xFlowUB As Integer, yTDHUB As Integer
    Dim i As Integer
    Dim Head1 As Double, Test As Double
    Dim coeff()
    
'Check Inputs
'Here I have several error checking routines to ensure the values passed to the function 
'are acceptable.
    
'Begin coeff and PumpHead calculation
    Select Case Order
        Case 2
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2})")
            PumpHead = coeff(1) * Flow ^ 2 + coeff(2) * Flow + coeff(3)
        Case 3
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2,3})")
            PumpHead = coeff(1) * Flow ^ 3 + coeff(2) * Flow ^ 2 + coeff(3) * Flow + coeff(4)
        Case 4
            coeff = Evaluate("LINEST(yTDH,xFlow^{1,2,3,4})")
            PumpHead = coeff(1) * Flow ^ 4 + coeff(2) * Flow ^ 3 + coeff(3) * Flow ^ 2 + coeff(4) * Flow + coeff(5)
         Case Else
            PumpHead = "ERROR!#"
    End Select

ExitFunction:
End Function

When I run the code above I get the error "Type mismatch". I tried converting the xFlow and yTDH values to an array but with no success.

Thanks in advance.

I believe I did attach the file "Excel Trendlines Test.xlsm". It appears on the webpage when I view the post.