IDW Interpolation with VBA

alexb · **Joined:** Mon Oct 25, 2010 2:08 pm **Posts:** 2

Hi,

First of all let me say that I'm not a programmer but I'm fascinated but such a great resource of recipes that ALGLIB provides. What I'd like to achive is the following:

I have a number of experimentally measured values Z at certain X and Y.
They are not uniformly spaced, i.e. the difference between one Y and the other is not always the same (also the Xs are not uniformly spaced).
I'd like to interpolate the Z values for certain X and Y values.

I plan to achive that by using VBA in Excel (that's the only thing I have available). Is it correct that the IDWBuildModifiedShepardR would be a good starting point for that?

Are there any example files that you know of that could help me getting started. I know this must sound trivial for you experts. There was a great website for the splines by http://newtonexcelbach.wordpress.com which helped me a lot but I couldn't find anything similar for the IDWInterpolation.

Thanks very much for any advice,

Alex

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

Hello!

Unfortunately, IDW is weakly documented feature, and there are serious problems with VBA documentation too. ALGLIB project includes tools to generate examples in multiple languages, but these example use console to output results - and VBA has no console.

I recommend to start from something simple, like R-based variant of algorithm (created using IDWBuildModifiedShepardR() function). After you get familiar with it, you can try moving to advanced topics.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Alex - thanks for the comment (I run the Newton Excel Bach blog). I've been having a play with the IDW interpolation functions, but I won't have time to post anything on them in the near future.

I suggest you have a look at the Excel VBA code for the ALGLIB functions at my blog, which should give you an idea for convenient ways to transfer data from Excel into the ALGLIB functions and back again. The statistics functions (http://newtonexcelbach.wordpress.com/20 ... functions/) might be a good place to start because the actual functions are quite simple.

alexb · **Joined:** Mon Oct 25, 2010 2:08 pm **Posts:** 2

Hi Doug and Sergey,

Thanks for your replies. Doug, I'm really impressed by your blog. Many people must have benefited from your work on integrating ALGLIB into easy to use Excel worksheets (which is especially great for people like me who only have Excel and can't program anything themselves). I got the statistics file and I think I get the idea.

The function VarAToDouble1D_0 is vital since it converts between Excel "ranges" and somthing ALGLIB can digest.

I copied that function into my module and tried to feed my data range to IDWBuildModifiedShepardR. I tried to keep it as simple as possible and fixed the number of nodes, dimensions, etc.

Code:

Function VarAtoDouble1D_0(XL_A As Variant, ByRef Cpp_A() As Double, ByRef Nrows As Long, ByRef Ncols As Long) As Long
    Dim i As Long, j As Long, LB As Long

    On Error GoTo iErr
    If TypeName(XL_A) = "Range" Then XL_A = XL_A.Value2
    LB = LBound(XL_A)
    Nrows = UBound(XL_A)
    Ncols = UBound(XL_A, 2)

    ' Copy 2D base 1 variant array to 1D base 0 double array
    ReDim Cpp_A(0 To Nrows * Ncols - 1)
    For i = 1 To Nrows
        For j = 1 To Ncols
            Cpp_A((i - 1) * Ncols + j - 1) = XL_A(i, j)
        Next j
    Next i
    VarAtoDouble1D_0 = 0
    Exit Function

iErr:
    VarAtoDouble1D_0 = 1
End Function

Public Function AL_Interpol(ByRef XA As Variant)
    Dim XA0() As Double, M As Long, N As Long, O As Long, Ncols As Long, Rtn As Long


Rtn = VarAtoDouble1D_0(XA, XA0, N, M)


Call IDWBuildModifiedShepardR(XA, 3354, 2, 10, ???)

End Function

My data set has 3354 values: 1118 x, 1118 y, and 1118 z; therefore 2 dimensions (right?) and I chose R to be 10.
I'm still not quite sure how to deal with the interpolants (???). The only thing I want to get to work right now is feed my data points into the Shepard algorithm. Isn't all it needs a range of x,y, and z data (in my case)? I don't mind if all the other parameters are 'hard coded' for the moment, I can change that later.

Guys, if you can't be bothered helping a newbie like me, it's really not a problem at all. You did all the great work already and maybe I just have to wait for another update in the newtonexcelbach blog that covers interpolation of surfaces for irregular data sets.

Thanks,
Alex

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

OK, here some quick recommendations:

0. yes, everything Shepard-R needs is just data and R - radius of nearest neighbor search

1. you should store all your data in one big matrix:
X0 Y0 Z0
X1 Y1 Z1
...

2. you should select good R. It should have order of magnitude close to average distance between points, and be so large than on average about 10-15 points fall into circle with radius R placed at random point in your data.

3. then you create interpolant by calling IDWBuildModifiedShepardR

4. after interpolant was built, you can work with it using other ALGLIB functions. If you want to evaluate its value at some arbitrary point, you can use IDWCalc(). Never try to access its fields directly - always use IDWCalc().

5. If you have set of scattered points where you want to evaluate interpolant, you should call IDWCalc() for each of these points.

Hope this will help you. If you have any additional questions, feel free to ask.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Alex - a couple of quick points:

- Rtn = VarAtoDouble1D_0(XA, XA0, N, M)

copies the data in the Variant array, XA, into a 1D base 0 Double array, XA0, so you should be using XA0 in the call to the ALGLIB routine, not XA.

- But it looks like the IDW routine needs a 2D array, not 1D, so the routine would need to be modified to do that.

I'll have a better look at this as soon as I have some time. Maybe next week.

forum.alglib.net

Forum rules

IDW Interpolation with VBA

Who is online