Trouble with Add-In

[chrisk0420]

I have a UDF that is stored as an Add-in.

Here is the code:

[FONT=Courier][COLOR=#00007f]Option [/COLOR][COLOR=#00007f]Explicit[/COLOR][/FONT]
 
[FONT=Courier][COLOR=#00007f]Function[/COLOR] TEST(returns [COLOR=#00007f]As[/COLOR] Range, [COLOR=#00007f]Optional[/COLOR] pr_Year [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Double[/COLOR] = 1)[/FONT]
 
[FONT=Courier]TEST = (Evaluate("=PRODUCT(1+" & returns.Address & "/100)") ^ (pr_Year / (returns.Rows.Count + returns.Columns.Count - 1)) - 1) * 100[/FONT]
 
[FONT=Courier][COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Function[/COLOR][/FONT]

I have this UDF saved as an Add-In. 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 cell with the formula.

If I store the UDF in the workbook itself it works fine with multiple worksheets. The problem is only when I store it as an Add-In.

I researched and tried many different options and none seem to work.

Does anyone have advice as to why this may not be working or can point me in the right direction as to where I might be able to find the answer?

Thanks in advance for your help.