Title: #Value error using custom function in Excel
Tags: excel;function;add-in;user-defined-functions;vba
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.

Comment: btw, those `(y2–y1)`, etc **should** have autocorrected to `(y2 - y1)` if the VBE had recognized the maths operation.

Comment: Also with Option Explicit you get a compile error (variable not defined)

Comment: Your `-` are not actual minus, code 150 vs code 45.  highlight each and replace it with an actually typed `-`

Here is the accepted answer: 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.

Comment for this answer: 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.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:04/05/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel><function><add-in><user-defined-functions><vba>', 'Title': '#Value error using custom function in Excel', 'ViewCount': 302, 'id': 43238172, 'user_AboutMe': None, 'user_AccountId': 6449768.0, 'user_DisplayName': 'BunnyKnitter', 'user_Reputation': 155.0, 'user_Views': 15.0, 'user_id': 5294214.0}}