Excel VBA Cubic Spline Interpolation

[wengkey95]

Hi Guys,
By referring to the older post, I found something below as Cubic Spline Interpolation. The formula I found was s(x) = a(x-xi)^3+ b(x-xi)^2 + c(x-xi) + d, I would like to understand how it translate to the algorithm below.

Much Appreciate.

Function spline(periodcol As Range, ratecol As Range, x As Range)
Dim period_count As Integer
Dim rate_count As Integer
period_count = periodcol.Rows.Count
rate_count = ratecol.Rows.Count
If period_count <> rate_count Then
spline = "Error: Range count dos not match"
GoTo endnow
End If

ReDim Xin(period_count) As Single
ReDim Yin(period_count) As Single
Dim c As Integer
For c = 1 To period_count
Xin(c) = periodcol(c)
Yin(c) = ratecol(c)
Next c
Dim n As Integer
Dim i, k As Integer
Dim p, qn, sig, un As Single
ReDim u(period_count - 1) As Single
ReDim yt(period_count) As Single
n = period_count
yt(1) = 0
u(1) = 0


'calculate the derivatives for cubic interpolation
For i = 2 To n - 1
sig = (Xin(i) - Xin(i - 1)) / (Xin(i + 1) - Xin(i - 1))
p = sig * yt(i - 1) + 2
yt(i) = (sig - 1) / p
u(i) = (Yin(i + 1) - Yin(i)) / (Xin(i + 1) - Xin(i)) - (Yin(i) - Yin(i - 1)) / (Xin(i) - Xin(i - 1))
u(i) = (6 * u(i) / (Xin(i + 1) - Xin(i - 1)) - sig * u(i - 1)) / p

Next i

qn = 0
un = 0
yt = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1) 'last knot boundary condition


For k = n - 1 To 1 Step -1
yt(k) = yt(k) * yt(k + 1) + u(k) 'second derivatives
Next k


'now eval spline at one point
Dim klo, khi As Integer
Dim h, b, a As Single
klo = 1
khi = n


Do
k = khi - klo
If Xin(k) > x Then
khi = k
Else
klo = k
End If
k = khi - klo
Loop While k > 1


h = Xin(khi) - Xin(klo)
a = (Xin(khi) - x) / h
b = (x - Xin(klo)) / h
y = a * Yin(klo) + b * Yin(khi) + ((a ^ 3 - a) * yt(klo) + (b ^ 3 - b) * yt(khi)) * (h ^ 2) / 6


spline = y
endnow:
End Function