I have written a function and when I call it in Excel, it returns me #value! error.
Function interpolator(maturities As Variant, rates As Variant, currencies As Variant, ccy As String, t_end As Double)
Dim xArr() As Double
Dim yArr() As Double
Dim xLen As Integer, currLen As Integer
xLen = UBound(maturities) - LBound(maturities) + 1
currLen = UBound(currencies) - LBound(currencies) + 1
ReDim xArr(1 To xLen, 1)
ReDim yArr(1 To xLen, 1)
'defining and filling the arrays with inputs
Dim m As Integer, k As Integer
m = 1
For k = 1 To currLen
If currencies(k, 1) = ccy Then
xArr(m, 1) = CInt(maturities(m, 1))
yArr(m, 1) = CDbl(rates(k, 1))
m = m + 1
End If
Next
' Check tenor is within range, then execute
If t_end = xArr(LBound(xArr), 1) Then
interpolator = yArr(LBound(yArr), 1)
Else
Dim n As Integer
For n = 1 To xLen
If xArr(n, 1) >= t_end Then
interpolator = yArr(n - 1, 1) + ((t_end - xArr(n - 1, 1)) * (yArr(n, 1) - yArr(n - 1, 1)) / (xArr(n, 1) - xArr(n - 1, 1)))
Exit For
End If
Next
End If
End Function
Sub test()
Dim maturities As Variant, rates As Variant, currencies As Variant
maturities = Worksheets("Static Data").ListObjects("Table_TenorTable").ListColumns("days").DataBodyRange.Value2
rates = Worksheets("Static Data").ListObjects("Table_TenorTable20").ListColumns("CCYYield").DataBodyRange.Value
currencies = Worksheets("Static Data").ListObjects("Table_TenorTable20").ListColumns("Currency").DataBodyRange.Value
MsgBox interpolator(maturities, rates, currencies, "USD", 31)
End Sub
interpolator(maturities, rates, currencies, "USD", 31) works fine but when I call it via Excel, it give me error.
I am calling in Excel like this:
=interpolator(Table_TenorTable[days],Table_TenorTable20[CCYYield],Table_TenorTable20[Currency],LEFT([@[Commodity_Group]],3),[@[T (in Tenors)]])
Need some guidance in solving this.