How to aggregate profits in Excel UDF using a product formula

Question

I am trying to put the formula below in UDF so that I can get cumulative revenue when aggregating monthly returns.

In excel, the formula should be recognized as an array, so when I type it, 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 simply enter returns_calc() and select a range that will fit into the variable below.

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

Answer 1

You can use the [ ] notation in Application.Evaluate to calculate array formulas in VBA. The 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 by changing it to accept a range in a function, you can also do this

 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 

SCREENSHOT EXAMPLE

Answer 2

Something like that

 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