UDF only works with named range

[chrisk0420]

I have a function that only works if I have a range named returns. I need it to work when an range is chosen for the argument

Function test(returns)

test = Evaluate("=PRODUCT(1+returns/100)")

End Function

Any help would be greatly appreciated

 

[Jonmo1]

Try

Function test(returns As Range)

test = Evaluate("=PRODUCT(1+" & returns.Address & "/100)")

End Function

 

[chrisk0420]

Perfect, Thanks!!

 

[chrisk0420]

The above function now works fine as long as I store the code in the workbook it is being used in. When I save it as an XLA file, it works fine in a workbook with one worksheet. With Multiple worksheets it still calculates fine while the workbook is open, but once I close and reopen it the only worksheet that calculates correctly is the one I was in when I saved it. The rest of the sheets don't calculate until I manually recalculate each sell with the formula...

I have tried adding application.volatile, adding code to the workbook open to force all formulas to recalculate. Nothing works

Any help would be greatly appreciated