Examples of computing interpolation by cubic spline function using XLPack are shown below.
Example: Logarithm table
Mathematical tables such as logarithm table were used in past days.
The following table is the part of natural logarithm table.
We consider to find the values of logarithm for the arguments which are not tabulated, for example ln(1.85).
Two methods using worksheet function WPchse, WPchfe and VBA subroutine Pchse, Pchfe are described below.
Solving problem with worksheet function
Enter the data (xi, yi) into the appropriate cells in the worksheet. xi must be in increasing order. Then select N (= number of data points) cells where the spline coefficients d are to be entered.
Click fx in the formula bar. Then select WPchse worksheet function from category XLPack.
Supply appropriate values for the requested parameters N (number of data points) (= 8 for this example), X() (range of data x), and Y() (range of data y).
Then press Ctrl+Shift+Enter.
Enter values of argument x where the interpolated values are to be computed in the other cells (for example, n = 1.55, 1.65, …, 2.15). Then compute the function values for these arguments by using WPchfe worksheet function. WPchfe computes the function value from the input parameters Ne (number of evaluation points) (= 7 for this example), Xe() (range of point data to be evaluated), N (number of data points used by WPchse) (= 8 for this example), X() (range of data x used by WPchse), Y() (range of data y used by WPchse), and D() (range of spline coefficients computed by WPchse).
Now we obtained the values for the arguments which are not tabulated.
Although data points are equally spaced in this example, they may be unequally spaced. However, the interpolated values for the interval with large space may be less accurate than those for short intervals. The values for the arguments of outside the data points (extrapolated values) can be computed but may be less accurate.
Solving problem with VBA program
The following is an example code using VBA library subroutine Pchse and Pchfe.
Sub Start() Const NMax = 10 Dim N As Long, Ne As Long, I As Long, Info As Long Dim X(NMax) As Double, Y(NMax) As Double, D(NMax) As Double Dim Xe(NMax) As Double, Fe(NMax) As Double '--- Input data N = 8 For I = 0 To N - 1 X(I) = Cells(4 + I, 1) Y(I) = Cells(4 + I, 2) Next '--- Compute coefficients of a cubic spline Call Pchse(N, X(), Y(), D(), Info) If Info <> 0 Then MsgBox "** Error in Pchse ** Info = " + Str(Info) Exit Sub End If '--- Compute interporated values Ne = N - 1 For I = 0 To Ne - 1 Xe(I) = Cells(4 + I, 4) Next Call Pchfe(N, X(), Y(), D(), Ne, Xe(), Fe(), Info) If Info <> 0 Then MsgBox "** Error in Pchfe ** Info = " + Str(Info) Exit Sub End If '--- Output result For I = 0 To Ne - 1 Cells(4 + I, 5) = Fe(I) Next End Sub
The following result was obtained by running this program.