#Value error using custom function in Excel

Question

I'm getting a #VALUE error when I try to call my custom function. All it is supposed to do is a bit of math. Does anyone see what could be wrong here?

I copied this one off the internet:

Source:

http://www.engineerexcel.com/linear-interpolation-vba-function-in-excel/

Function LININTERP(x, xvalues, yvalues)

'x and y values must be in ascending order from top to bottom.
'x must be within the range of available data.

x1 = Application.WorksheetFunction.Index(xvalues, Application.WorksheetFunction.Match(x, xvalues, 1))
x2 = Application.WorksheetFunction.Index(xvalues, Application.WorksheetFunction.Match(x, xvalues, 1) + 1)

y1 = Application.WorksheetFunction.Index(yvalues, Application.WorksheetFunction.Match(x, xvalues, 1))
y2 = Application.WorksheetFunction.Index(yvalues, Application.WorksheetFunction.Match(x, xvalues, 1) + 1)

LININTERP = y1 + (y2–y1) * (x–x1) / (x2–x1)
End Function

This is a simplified version I made thinking that the worksheet function calls may be causing the error:

Function LININTERP(x, x1, x2, y1, y2)
  LININTERP = y1 + (y2–y1) * (x–x1) / (x2–x1)
End Function

my test data in an unrelated workbook: (All formatted as "General")

A1: 633
A2: 634
B1: 14.968
B2: 15.024
C1 (my x): 633.6

Just plugging the actual math into a cell works as expected. Calling the function throws the #VALUE error.

My function is saved in a module in a workbook that I have saved and added to Excel as an Add-In.

Your - are not actual minus, code 150 vs code 45. highlight each and replace it with an actually typed - — Scott Craner
– Scott Craner, Commented Apr 5, 2017 at 17:59
btw, those (y2–y1), etc should have autocorrected to (y2 - y1) if the VBE had recognized the maths operation. — user4039065
– user4039065, Commented Apr 5, 2017 at 18:12
Also with Option Explicit you get a compile error (variable not defined) — Tom Sharpe
– Tom Sharpe, Commented Apr 5, 2017 at 19:02

user4039065 · Accepted Answer · 2017-04-05 18:06:52Z

3

My sampling of your formula and data threw an error on the hyphens not being interpreted as 'minus signs'. In fact, they come up as unicode 8211. Retyping them, declaring the vars as variants and removing the ...WorksheetFunction... fixed the problem.

Function LININTERP(x, xvalues, yvalues)
    Dim x1 As Variant, x2 As Variant, y1 As Variant, y2 As Variant

    'x and y values must be in ascending order from top to bottom.
    'x must be within the range of available data.

    x1 = Application.Index(xvalues, Application.Match(x, xvalues, 1))
    x2 = Application.Index(xvalues, Application.Match(x, xvalues, 1) + 1)

    y1 = Application.Index(yvalues, Application.Match(x, xvalues, 1))
    y2 = Application.Index(yvalues, Application.Match(x, xvalues, 1) + 1)

    LININTERP = y1 + (y2 - y1) * (x - x1) / (x2 - x1)
End Function

Moral of the story: Don't trust everything you find on the internet.

answered Apr 5, 2017 at 18:06

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

btw² - Removing WorksheetFunction and passing the returned value back to a variant allows you to check for errors like If IsError(x1) Then. Keeping WorksheetFunction removes this ability for error-handling.

Collectives™ on Stack Overflow

#Value error using custom function in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related