Custom Function

[smpatty08]

I am trying create custom functions for the individual coefficients of a quadratic equation separately to avoid using the LINEST function and creating an array. I have a sub that will display the information I want correctly in a msgbox, but when I create the function I get #VALUE ! error. Here is what I have so far.

Function QuadA(y As Range, x As Range) As Double
A = Application.Evaluate("=LINEST(y,(x)^{1,2})")
QuadA = Format(A(1), "0.###")
End Function

Function QuadB(y As Range, x As Range) As Double
B = Application.Evaluate("=LINEST(y,(x)^{1,2})")
QuadB = Format(B(2), "0.###")
End Function

Function QuadC(y As Range, x As Range) As Double
C = Application.Evaluate("=LINEST(y,(x)^{1,2})")
QuadC = Format(C(3), "0.###")
End Function

Sub Quad()
x = Application.Evaluate("=linest(E6:E11,(D6:D11)^{1,2})")
MsgBox "Equation is y=" & Format(x(1), "0.###") & "x2+" & Format(x(2), "0.###") & "x+" & Format(x(3), "0.###")
End Sub

The Sub Quad() works and displays the correct information, but none of the function display a number. I think the problem is that the LINEST function doesn't know what property to use, but even if I put .Address after y and x it stills creates an error. Is there somebody obvious that I am doing incorrectly? Any help is appreciated

 

[RoryA]

y and x have no meaning as part of a formula string. You could use:

Function QuadA(y As Range, x As Range) As Double
A = y.worksheet.Evaluate("=LINEST(" & y.address & ",(" & x.address & ")^{1,2})")
QuadA = Format(A(1), "0.###")
End Function

 

[smpatty08]

y and x have no meaning as part of a formula string. You could use:

Function QuadA(y As Range, x As Range) As Double
A = y.worksheet.Evaluate("=LINEST(" & y.address & ",(" & x.address & ")^{1,2})")
QuadA = Format(A(1), "0.###")
End Function

Wow that was a silly oversight. This works perfectly! Thanks for your help