How to aggregate returns in an excel UDF using the product formula

Question

I am trying to put the below formula into a UDF so that I can get a cumulative return when I aggregate monthly returns.

In excel the formula has to be recognized as an array so when I type it in I press Ctrl + Shift + Enter to get the {} brackets around the formula.

Does anyone know how to do this?

I want to be able to just type in returns_calc() and select the range that would fit into the returns variable below.

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

Answer 1

You can use the [ ] notation in Application.Evaluate to calculate Array Formulas in VBA. Your above formula can be called in VBA in just 1 line as shown below

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

Now modifying it to accept a range in a function, you may do this as well

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

EXAMPLE SCREENSHOT

Answer 2

Something like this

Public Function Range_Product(theRange As Variant)
    Dim var As Variant
    Dim j As Long

    var = theRange.Value2
    Range_Product = 1#
    For j = LBound(var) To UBound(var)
        Range_Product = Range_Product * (1 + var(j, 1) / 100)
    Next j

    Range_Product = (Range_Product - 1) * 100
End Function