1

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.

lakshmen
  • 28,346
  • 66
  • 178
  • 276
  • I'm not positive that this is the issue, but `maturities`, `rates`, and `currencies` are of the type, `Range`. I would think you'd need to convert them to `Array` before you can use `UBound` and `LBound` on them. – Ben Osborne Feb 13 '22 at 13:50
  • See https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba for a function that converts a range to an array. – Ben Osborne Feb 13 '22 at 14:15

0 Answers0