linear interpolation function vba code

[xelhelp]

Hi,

I have an interpolation function that I have tried to write in VBA. The results of the function are correct when the given X value (in my case sigma) is equal to or outside the bounds of the highest or lowest sigma. But when the given X value or sigma for which I'm trying to interpolate is between the highest and lowest sigma I get incorrect results. I'm not sure where the code is wrong or why its giving incorrect results for this subset of scenario. I'd appreciate any insight. I have attached a sheet with sample data that also contains the VBA code under module1. I will also include the code below.

Thanks

examplesheet.xlsm

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

The formula in F2 and copied down is

=LInterp(E2, $B$2:$B$72, $A$2:$A$72)

Please Login or Register  to view this content.

[shg]

Also, your data should be sorted ascending by sigma (the independent variable).

As an alternative, the data is well approximated by a parabola. You could regress that and use the coefficients to interpolate.

[xelhelp]

hi shg,

thank you for your post.

is there a way to tweak my original code to make it work? im new to vba and im trying to learn...i was able to write the code by myself and it seems to almost work...except for the case when X is between the range of lowvol to highvol

thanks again

[xelhelp]

the thing is that i only want it to interpolate between the closest value above and below the given sigma...that is why i used the match function to find the closest below and add +1 to find the value just above my given sigma

thanks

[xelhelp]

do i maybe need a next statement in there somehow? (in my original code)

[xelhelp]

i feel like there is something simple that is probably wrong in my code...

[MrShorty]

Cannot use a Next statement without a For statement. Since I see no For statements, there's no need for a Next statement.

I apologize for being lazy. I looked at your code and decided I didn't have the time or energy to try to pick it apart. A few things I do see:

1) There are a lot of If/Elseif's in that block if. When I've written linear interpolation functions I consider 3 scenarios: targetsigma<min(sigmas), targetsigma>max(sigmas), or targetsigma is between min and max, so I would suggest that your main block if should only need 2 elseif's beyond the initial if.

2) I also think you could do this with fewer arguments, since lowsigma, highsigma, lowvol, etc. can be extracted from the sigmas and vols arguments.

3) Have you tried stepping through the function? By getting VBA into debug mode, you can set up some watches and follow the code to see where it is getting the wrong value. With a UDF, this is a little trickier than with a sub function. Go into your code and set a break point (F9 key). Then go into the spreadsheet and get that cell to calculate. F9 might work, or select the cell containing the function and hit F2 and enter. You should be brought into the editor with your code execution paused at the line where you set the breakpoint. Add watches for the important variables and see if all the values are what you expect. Then use F8 to step through the function to see how the variables are changing and see if you can see where it is calculating incorrectly.

4) in particular, check the main interpsisigma statement. There are a lot of parentheses in there and it would be easy to have gotten one out of place.

[xelhelp]

pretty stumped on this...not sure where my code is wrong. I've looked over it several times...

[xelhelp]

thanks MrShorty....u were right I had my parentheses in interpsigma formula in the wrong place...

below is the correct formula...

Please Login or Register  to view this content.

[xelhelp]

hi,

i have made corrections and the function seems to work fine when there are no "#VALUE!" or other errors in the "vols" column... realistically there will be times when my spreadsheet will in fact have "#VALUE!" in some cells in the "vols" column... please have a look at the example sheet i attached...
examplesheet2.xlsm

below is also my code and you will also find it in module1...

for example the target sigma of -2 in column E is between the -2 and -1.87 sigma in column B...and there are no errors in the cells to the left of -2 sigma and -1.87 sigma...the final result shouldn't throw errors....

similarly ....the 0 target sigma in column E is between -0.04 and 0.07 sigma listed in column B which again have values instead of errors in the cells to the left of them....

im not sure why the code is throwing an error...

please note that when i clear contents in cells A2:A13 and A61:A72 the results are correct...actually u only need to clear contents in A2:A7, A9:A11, A13 and A71 it populates correct results in column F... but if the two closest sigmas in column B have values and no errors to the left of them it shouldn't matter if there are errors in other cells in column A

id appreciate any help

thanks

Please Login or Register  to view this content.

[xelhelp]

its strange that when you clear some of the error values in column A like i stated above it populates the correct results...im not sure where to begin to correct this issue