Excel VBA Seek Error Solution

[DreamV2019]

I have been working with a VBA function called "ForwardCurve" in excel 2016. The code is not very complex or large, in fact its fairly small. Unfortunately, the function is resulting in an error message that I hoped someone could help shed light on. The error message states, " Microsoft Visual Basic for Application Compile Error: Variable not defined". I'm specifically looking for a look over with the code to make sure there isn't something minor missing or needing correcting. I have attached the excel file with the VBA code. There are quick details on the first worksheet of the excel doc. The second worksheet has the model I'm attempting to use the function/code. Lastly, the third worksheet has an identical sample model with all the correct results without the function/code. Thanks! ForwardCurve Function.xlsmForward Curve Function.docx

[Paul_Hossler]

Function ForwardCurve(dblTime As Double, Term As Variant, SpotRates As Variant)
    'Term and SpotRates are variant data type
    Dim l As Integer ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< that's an L, but you're using I in the loop

Also SplineInterpolation() is not defined in the workbook

[DreamV2019]

Option Base 1
Option Explicit
Function ForwardCurve(dblTime As Double, Term As Variant, SpotRates As Variant)
    'Term and SpotRates are variant data type
    Dim l As Integer
    Dim vecForwardRates() As Double
    ReDim vecForwardRates(SpotRates.Count - 1)


    'Create forward rate curve and fill vecForwardRates()
   i = 1
    For i = 1 To UBound(vecForwardRates)
           vecForwardRates(l) = ForwardRate(dblTime, dblTime + Term(l), Term, SpotRates)
    Next i
    ForwardCurve = Application.WorksheetFunction.Transpose(vecForwardRates)
End Function
Private Function ForwardRate(dbt1 As Double, dbt2 As Double, rngTerm As Variant, rngMid As Variant)
Dim rt1, rt2 As Double
  'find rt1, rt2 (appropriate interpolated rates) using spline interpolation method
rt1 = SplineInterpolation(dbt1, rngTerm, rngMid)
rt2 = SplineInterpolation(dbt2, rngTerm, rngMid)
 'calculate forward rate f(rt1,rt2)
ForwardRate = rt2 * (dbt2 / (dbt2 - dbt1)) - rt1 * (dbt1 / (dbt2 - dbt1))
End Function

Thanks for the response Paul, would anything else require an L instead of an i in the code?
Do you know where I could obtain SplineInterpolation () vba code, ( a book so I may compare to the one I have? Sorry I didn't include this, as the ForwardFunction was my concern.
Regards

[Paul_Hossler]

No idea what SplineInterpolution() is supposed to do, but Google is your friend. There is an TREND() function that you could look at

Capture2.JPG
Some sugguestions

1. 'i' is more common than 'l' so I changed to 'i'

2. Long is recommended over Integer

3. If you don't explicitly Dim each variable, they default to Variant

Dim rt1 As Double, rt2 As Double

Option Base 1
Option Explicit


Function ForwardCurve(dblTime As Double, Term As Variant, SpotRates As Variant) As Variant
    'Term and SpotRates are variant data type
    Dim i As Long   '   <<<<<<<<<<
    Dim vecForwardRates() As Double
    ReDim vecForwardRates(SpotRates.Count - 1)
    'Create forward rate curve and fill vecForwardRates()
    i = 1
    For i = 1 To UBound(vecForwardRates)
        vecForwardRates(i) = ForwardRate(dblTime, dblTime + Term(i), Term, SpotRates)
    Next i
    
    ForwardCurve = Application.WorksheetFunction.Transpose(vecForwardRates)
End Function

Private Function ForwardRate(dbt1 As Double, dbt2 As Double, rngTerm As Variant, rngMid As Variant) As Variant
    Dim rt1 As Double, rt2 As Double    '   <<<<<<<<<<<<
    'find rt1, rt2 (appropriate interpolated rates) using spline interpolation method
    rt1 = SplineInterpolation(dbt1, rngTerm, rngMid)
    rt2 = SplineInterpolation(dbt2, rngTerm, rngMid)
    'calculate forward rate f(rt1,rt2)
    ForwardRate = rt2 * (dbt2 / (dbt2 - dbt1)) - rt1 * (dbt1 / (dbt2 - dbt1))
End Function

The error message and the highlight is pretty clear that 'i' is not defined

That's the reason I always use Option Explicit, since the macro would run, but the answers would be wrong (I'll take all the help I can get)

Capture.JPG

[DreamV2019]

Thanks Paul for all the help! I saw that function on youtube- https://www.youtube.com/watch?v=e9uU...ature=youtu.be

I found another forward rate function that is directly from a book for Excel, although I did use another SplineInterpolate UDF for the non-given years, it works well. Hope the pictures below are expandable when you click on them. The only thing that makes the youtube function better is the ability to set the future rate at different terms (.50,1,2,3,etc), instead of only one year for the model in the pictures. Regards.
Capture6.JPGCapture7.JPG