# VBA function - incorrect syntax - help?

#### ChrisofRedrock

##### New Member
Hello:

I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is

Function INTSPOT(spots, year)
'Interpolates spot rates to year
Dim i As Integer, spotnum As Integer
spotnum = spots.Rows.Count
If Application.WorksheetFunction.Count(spots) = 1 Then
'Single rate given
INTSPOT = spots
Else 'Term structure given
If year <= spots(1, 1) Then
INTSPOT = spots(1, 2)
ElseIf year >= spots(spotnum, 1) Then
INTSPOT = spots(spotnum, 2)
Else
Do
i = i + 1
Loop Until spots(i, 1) > year
INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _
(year - spots(i - 1, 1)) / _
(spots(i, 1) - spots(i - 1, 1))
End If
End If

End Function

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Andrew Poulsom

##### MrExcel MVP
What formula are you trying that gives a syntax error?

#### ChrisofRedrock

##### New Member
The INTSPOT formula

#### Norie

##### Well-known Member
chris

What errors are you getting?

That UDF works fine for me, though it returns a value of 0.

But no errors.

PS I think Andrew is asking how you are using the formula on the worksheet.

#### ChrisofRedrock

##### New Member
Thanks for the clarification. Sorry about that Andrew. I'm using it to try and calculate for a non-flat interest rate structure using the following parameters.

t Spot Rate Year
0.08 3.50% 2.5
0.25 4.00%
0.5 4.50%
2 5.00%
3 5.50%
5 6.00%
10 6.50%

The error I'm getting is that on the sheet, Excel doesn't recognize the function (before that it said I had a syntax error). Is there anything else I have to do in order to implement the UDF?

#### Andrew Poulsom

##### MrExcel MVP
What formula have you tried? To avoid the #NAME? error the function needs to be in a General module in the same workbook.

#### ChrisofRedrock

##### New Member
I've tried the INTSPOT formula and it is in the same workbook. It's still giving me the name error. Is there any specific command I need to put in the function line to alert excel to the fact it is a UDF(just to make sure I'm not forgetting to do something stupid)?

#### ChrisofRedrock

##### New Member
=intspot(C4:D10, 2.5)

#### Norie

##### Well-known Member
Where exactly is the code located?

Replies
4
Views
305
Replies
3
Views
411
Replies
3
Views
61
Replies
1
Views
162
Replies
7
Views
230

1,190,766
Messages
5,982,817
Members
439,798
Latest member
tangojuliet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back