VBA function - incorrect syntax - help?

[ChrisofRedrock]

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

 

[Andrew Poulsom]

What formula are you trying that gives a syntax error?

 

[ChrisofRedrock]

The INTSPOT formula

 

[Norie]

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]

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]

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

 

[ChrisofRedrock]

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)?

 

[Andrew Poulsom]

Post your formula.

 

[ChrisofRedrock]

=intspot(C4:D10, 2.5)

 

[Norie]

Where exactly is the code located?