VBA Macro to Interpolate Between Values

  • I have a worksheet of (x,yn) numerical data where x are in data steps of anywhere from 10-30. Yn are arbitrary data ranging anywhere from 0.00 to 100.00. I currently have a program where I import the data and resample x using an weighted average interpolation method to yield data steps of 1 with respective interpolated y data. It is an easy process, but I would like to implement a macro for when I don't have access to the program. Below is a sample of the data set (I have also attached a sample data set below):


    A B C D E
    Dept Qt Ca Ill Sme
    4030 64.00 1.60 29.20 5.20
    4070 64.87 3.00 27.67 4.47
    4100 60.50 2.33 32.43 4.80


    I would like a VBA macro function that would take the upper and lower bounds of each cell in column A (4030, 4070) to produce a new data set with interpolated values for columns B-E with a column A data step of 1 (4030, 4031, 4032,...). It would even be better if the function could be a cubic spline interpolation instead of a linear interpolation. Excel can graph the above data as a the cubic spline function using a point data graph, however I have found nothing in Excel that can interpolate and export the data out of Excel to look like what the visual graph displays.


    My experience with Excel/VBA is fairly limited but this will be a learning experience.


    Any help would appreciated.


    Best regards,
    Zach W.


    forum.ozgrid.com/index.php?attachment/63994/

  • Re: VBA Macro to Interpolate Between Values


    I believe that you need at least 3 if not 4 data points minimum for the CS. By only processing 2 points, you're correct in that you will get the same result as the LI due to the fact that there is only an origin and endpoint in which the interpolation is linear by default. From what I understand about CS is that it takes into account the preceding and next point between each given point to calculate the interpolation between each point.

  • Re: VBA Macro to Interpolate Between Values


    I've been using this macro for interpolation points between deviation surveys of a pipe path. Might give it a try:

  • Re: VBA Macro to Interpolate Between Values


    Yes of course, that makes sense. Did you have a look at the link? It has a workbook with a custom function in it which does the interpolation and looks to me to be right, but I'm not sure what I'm looking at.


    Or can try shooteGUB1's solution.

  • Re: VBA Macro to Interpolate Between Values


    Quote from StephenR;738155

    Yes of course, that makes sense. Did you have a look at the link? It has a workbook with a custom function in it which does the interpolation and looks to me to be right, but I'm not sure what I'm looking at.


    Or can try shooteGUB1's solution.


    Thanks. I'll take a better look at that today. I've tried to implement shooteGUB1's solution but was wasn't having much success. I'm afraid it's probably due to more my lack of background with VB than the effectiveness of the code.

  • Re: VBA Macro to Interpolate Between Values


    SOLVED. Somewhat...now I am working on the automation part. Thank you for you help on this one...and thanks for your help on the other post!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!