arrays - 如何将不确定的数组传递并循环给 VBA 中的函数？

Question

我的函数接受一个范围，比如无风险利率，并产生一系列贴现因子。问题似乎在循环中，有三个方面:

(a) 在数组上调用 fn，
(b) 指定数组点，
(c) 使用循环 i 作为 fn 参数。

最好的方法是在每个数组(i 点)周围使用循环，还是可以通过简单地调用函数来填充整个数组？

Function CreateDiscArray(RFR_array As Range)

    Dim MyArray() As Variant
    MyArray = RFR_array

    Dim xDimRate As Integer
    xDimRate = UBound(MyArray, 1)

    Dim TempArray() As Variant
    For i = 1 To xDimRate Step 1
        TempArray(i, 1) = DiscFact(MyArray(i), i)
    Next i

    CreateDiscArray() = TempArray()
End Function

.

Function DiscFact(Rate, Tenor)
    If Tenor < 1 Then DiscFact = (1 + Tenor * Rate)
    If Tenor >= 1 Then DiscFact = (1 + Rate) ^ (-Tenor)
End Function

即是否可以只调用而不循环:

CreateDiscArray = DiscFact(MyArray(,1), 1 to xDimRate)

Answer 1

您的原始代码存在一些问题

• 您没有使用 Option Explicit ，所以你不知道i未声明。

• 您没有标注 TempArray 的尺寸，因此您的代码无法分配给它的索引。

• 您正在引用myArray(i)由于有 2 个维度，这将失败，因此您必须使用 myArray(i,1) .

• 您正在使用i计数器(从 1 开始)作为主旨。这是一个糟糕的设计选择，因为您的期限并不总是一致的长度，并且您可能会遇到许多短期期限。此外，这是一个错误，因为 i >= 1将永远是TRUE

因此，要使您的原始函数可用代码:

Option Explicit

Function CreateDiscArray(RFR_array As Range)

    Dim MyArray() As Variant
    MyArray = RFR_array.Value

    Dim xDimRate As Integer
    xDimRate = UBound(MyArray, 1)

    ReDim TempArray(LBound(MyArray) To UBound(MyArray), LBound(MyArray, 2) To UBound(MyArray, 2)) As Variant
    Dim i As Long
    For i = 1 To xDimRate Step 1
        TempArray(i, 1) = DiscFact(MyArray(i, 1), i)
    Next i

    CreateDiscArray = TempArray
End Function

Function DiscFact(Rate, Tenor)
    'BUG: Tenor will always be >= 1
    If Tenor < 1 Then DiscFact = (1 + Tenor * Rate)
    If Tenor >= 1 Then DiscFact = (1 + Rate) ^ (-Tenor)
End Function

但这不是你的问题。正如其他人指出的那样，VBA 本身不支持任何内容，但您使用的是 Excel，因此您确实有一些选择:

首先，让我们通过添加 tenors 范围来修复 Tenor 错误。除了 rates 。男高音在 A1:A3费率在 B1:B3 。我们可以在 C1:C3 中使用数组公式如=IF(A1:A3<1,1+A1:A3*B1:B3,(1+B1:B3)^(-A1:A3))

    A   |   B  |   C
--+-----|------|-------------------------------------------------
1 | .5  | 99   | {=IF(A1:A3<1,1+A1:A3*B1:B3,(1+B1:B3)^(-A1:A3))}
2 | 1   | 97   | 
3 | 2   | 95   | 

并且，如果将命名范围命名为 Tenor 和 Rate，则可以将数组公式重新定义为 =IF(Tenor<1,1+Tenor*Rate,(1+Rate)^(-Tenor))

如果您确实希望此解决方案采用 VBA，则需要更改函数签名以接受期限和速率范围，然后使用 Application.Evaluate以及构造的公式，以获得结果数组。

使用不关心工作表或工作簿的笨拙解决方案:

Public Function DiscFactor(rates As Range, tenors As Range) As Variant

  Dim Rate As String
  Dim Tenor As String
  Rate = rates.Address
  Tenor = tenors.Address

  DiscFactor = Application.Evaluate("=IF(" & Tenor & "<1,1+" & Tenor & "*" & Rate & ",(1+" & Rate & ")^(-" & Tenor & "))")

End Function