Copy/Paste user-defined formula (in an Add-In) to new workbook results in #NAME

[MStepan]

I've recently been trying to consolidate VBA by leveraging Add-Ins. I THINK that might be at the root of this issue, but am obviously not positive (or I wouldn't be posting this).

I have a simple User-Defined function that just returns the NAME of the office user ("Mark Stepan") by retrieving the UserName property (CurrentUserName = Application.UserName). It works fine in an Excel workbook. However, I also have a need to be able to effectively "copy/paste values" to another workbook to save a snapshot of this file (actually worksheet). When I copy/paste the formula to a new workbook, I get #NAME . If I EDIT the formula (F2, Enter), that DOES resolve it. This tells me that Excel IS able to locate the function, etc. But why, when the initial copy/paste happens, does it not resolve like I'd expect? When doing this interactively and manually, it's not a huge deal (an "irritation" mostly) to fix, but this is actually being performed via VBA, so I need to understand what the issue is in order to come up with the best way to address it.

I'm using Office 2010 (Excel 2010) on a Terminal Server that's running Windows Server 2008 R2. In doing a quick test at home, Excel 2016 doesn't appear to act the same. That is, it resolves the formula correctly.

Thanks in advance for your thoughts and ideas.

Mark

 

[MStepan]

A little bit of additional information: I still can't get Excel 2010 to do what I want. I wasn't able to make Excel 2016 "fail". But Excel 2013 worked or didn't based on the following setting: Trust Center, External Content, Enable automatic update for all Workbook Links. That is, Excel 2013 seems to apply that setting in the scenario I'm in (copying a formula from one workbook to another). My reaction is still "hmmmmm". Maybe nobody else has ever done this? Thanks.