vba - 如何使用乘积公式在 excel UDF 中汇总 yield

Question

我正在尝试将以下公式放入 UDF 中，以便在汇总每月 yield 时获得累积 yield 。

在 excel 中，公式必须被识别为一个数组，所以当我输入它时，我按 Ctrl + Shift + Enter 来获取 {}公式周围的括号。

有谁知道如何做到这一点？

我希望能够只输入 returns_calc()并选择适合下面返回变量的范围。

{=(PRODUCT(1+returns/100)-1)*100}

Answer 1

您可以使用 [ ] Application.Evaluate 中的符号在 VBA 中计算数组公式。你上面的公式可以在VBA中调用只需 1 行 如下所示

Sub Sample()
    MsgBox Application.Evaluate(["=(PRODUCT(1+returns/100)-1)*100"])
End Sub

现在修改它以接受函数中的范围，您也可以这样做

Function returns_calc(rng As Range) As Variant
    On Error GoTo Whoa

    Dim frmulaStr As String

    frmulaStr = "=(PRODUCT(1+(" & rng.Address & ")/100)-1)*100"
    returns_calc = Application.Evaluate([frmulaStr])

    Exit Function
Whoa:
    returns_calc = "Please check formula string" 'or simply returns_calc = ""
End Function

示例屏幕截图