VBA Linest function for polynomial regression on horizontal range
 

Hello,
I made a function to calculate values and derivatives of polynomial
trends. It works perfect on vertical X en Y ranges but not on
horizontal ranges. I use the Dutch version of Excel 2007. Any ideas
what could clear the problem? I'll be very gratefull.

It goes like this :

Function Polytrend(Xas, Yas, Punt, Graad, ResultType, Optional Per) As
Double
Dim a6 As Double, a5 As Double, a4 As Double, a3 As Double, a2 As
Double, a1 As Double, a0 As Double
Dim Res1 As Double, Res2 As Double
Dim varr()
If IsMissing(Per) Or Per = 0 Then Per = 1
Select Case Graad
Case Is = 1
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1})")
a6 = 0: a5 = 0: a4 = 0: a3 = 0: a2 = 0
a1 = varr(1): a0 = varr(2)
Case Is = 2
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2})")
a6 = 0: a5 = 0: a4 = 0: a3 = 0
a2 = varr(1): a1 = varr(2): a0 = varr(3)
Case Is = 3
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3})")
a6 = 0: a5 = 0: a4 = 0
a3 = varr(1): a2 = varr(2): a1 = varr(3): a0 = varr(4)
Case Is = 4
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4})")
a6 = 0: a5 = 0
a4 = varr(1): a3 = varr(2): a2 = varr(3): a1 = varr(4): a0 =
varr(5)
Case Is = 5
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4,5})")
a6 = 0
a5 = varr(1): a4 = varr(2): a3 = varr(3): a2 = varr(4): a1 =
varr(5): a0 = varr(6)
Case Is = 6
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4,5,6})")
a6 = varr(1): a5 = varr(2): a4 = varr(3): a3 = varr(4): a2 =
varr(5): a1 = varr(6): a0 = varr(7)
End Select
Polytrend = a6 * Punt ^ 6 + a5 * Punt ^ 5 + a4 * Punt ^ 4 + a3 * Punt
^ 3 + a2 * Punt ^ 2 + a1 * Punt + a0


End Function