PDA

View Full Version : Excel VBA Seek Error Solution



DreamV2019
08-31-2018, 02:49 PM
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! 2280522806

Paul_Hossler
08-31-2018, 03:08 PM
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
09-01-2018, 01:00 PM
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? :dunno
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
09-01-2018, 03:06 PM
No idea what SplineInterpolution() is supposed to do, but Google is your friend. There is an TREND() function that you could look at

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

22809

DreamV2019
09-02-2018, 08:58 PM
Thanks Paul for all the help! I saw that function on youtube- https://www.youtube.com/watch?v=e9uUj4FBMeQ&feature=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.
2281422815