forum.alglib.net

hi,I'm a newer to VBA and alglib.I want to get interpolation result with VBA as use spline function in matlab.
So,I download your VBA alglib,and import the file "spline1d.bas",then I input the rawdata x,y in excel sheetcells.and call the sub,but I can't get the right resluts even with simple method-----Spline1DBuildLinear, then I think some code maybe wrong,e.g.:

For I = 0# To N - 2# Step 1
C.C(4# * I + 0#) = Y(I)
C.C(4# * I + 1#) = (Y(I + 1#) - Y(I)) / (X(I + 1#) - X(I))
C.C(4# * I + 2#) = 0#
C.C(4# * I + 3#) = 0#
Next I

Then I modify these codes as below:,and I get the right results.

For I = 0# To N - 2# Step 1
C.C(4# * I + 0#) = Y(I)
C.C(4# * I + 1#) = C.C(4# * I + 0#) + (Y(I + 1#) - Y(I)) / (X(I + 1#) - X(I)) * ((X(I + 1#) - X(I)) / (C.K + 1))
C.C(4# * I + 2#) = C.C(4# * I + 1#) + (Y(I + 1#) - Y(I)) / (X(I + 1#) - X(I)) * ((X(I + 1#) - X(I)) / (C.K + 1))
C.C(4# * I + 3#) = C.C(4# * I + 2#) + (Y(I + 1#) - Y(I)) / (X(I + 1#) - X(I)) * ((X(I + 1#) - X(I)) / (C.K + 1))
Next I

Wen I use Spline1DBuildcubic,and Spline1DBuildAkima,the results is incomprehensible. Attached file please find my excel file.

I don't know how to get result as I can get from matlab spline:
x=[5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 35];
y=[ 191.280463 191.371003 191.476958 191.598295 191.735474 191.886329 192.042291 192.221280 192.420932 192.632701 192.864056 193.115150 193.375755 193.641323 193.928297 194.235425 194.564179 194.904111 195.256993
195.626070 196.001560];
xxxx=5:0.01:25;
yyyy=spline(x,y,xxxx);

Please give me some advices.thank you in advance.

Your code tries to read data from spline1dinterpolant internals. You should use spline1dcalc() function to calculate value of a spline - read http://www.alglib.net/translator/man/ma ... line1dcalc

A few comments:

The first Google hit on "Akima spline" is: http://www.alglib.net/interpolation/spline3.php and this is useful for those trying to implement the routines when they didn't write them :)

The Akima spline gives results very close to the Matlab output.

The cubic spline in the posted spreadsheet is constrained to a slope of 1 at both ends, so it oscillates at the ends.
Spline1DBuildCubic X, Y, 21, 1, 1, 1, 1, C3
A better fit is given by setting the end curvature to zero:
Spline1DBuildCubic X, Y, 21, 2, 0, 2, 0, C3

And a few suggestions on the VBA code:
Dim I, J As Integer
creates I as a variant and J as an integer, which is presumably not what was intended. Also note that VBA converts intergers to longs anyway, and takes time doing this, so the more efficient code is:
Dim I as long, J as long

Writing to the spreadsheet one cell at a time really slows things down. It is better to write all the results to an array then write that to the spreadsheet in one operation. Similarly it is better to read input data into a variant array rather than read one cell at a time e.g.:

Dim CubResA(1 to 2000, 1 to 1) as double
Dim XI As Variant
..
XI = Range("c2:C2001").Value2
Spline1DBuildCubic X, Y, 21, 2, 0, 1, 0, C3
For J = 1 To 2000
CubResA(J, 1) = Spline1DCalc(C3, XI(J, 1))
Next J
Range("e2:e2001").Value2 = CubResA

That approach reduces the time for calculation of the cubic and akima splines for 2000 values from 0.3 seconds to 0.03 seconds.

Also in practice it is better to use range names rather than specific cell addresses, see here: http://newtonexcelbach.wordpress.com/20 ... orrection/

_________________
Doug Jenkins
http://newtonexcelbach.wordpress.com/

I have just posted on the AlgLib Spline functions at: http://newtonexcelbach.wordpress.com/20 ... functions/ including links to spreadsheet implementations of most of the 1D spline functions.

May be useful for those wanting to use the AlgLib functions in their own code (or use the downloaded functions directly).

_________________
Doug Jenkins
http://newtonexcelbach.wordpress.com/