How to use the product formula to aggregate the return in Excel UDF

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

You can use the [] notation in Application.Evaluate to calculate Array formula in VBA. You can call the above formula in only one line in VBA, as shown below

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

Now modify it to accept the range in the function, you can also do this Do

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

Sample screenshot

I am trying to put the following formula into the UDF in order to get a cumulative return when summarizing monthly returns.

In excel, the formula must be recognized as an array, So when I type in 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 input only returns_calc() and select the range that fits the returns variable below.

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

You can use the [] notation in Application.Evaluate to calculate the array formula in VBA. You can only Call the above formula in one line, as shown below

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

Now modify it to accept the range in the 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

Sample screenshot