I don’t use user defined functions in add-ins all that often, but I happen to have a particular add-in with one UDF. And I happened to have recently moved that add-in. And all my links happen to be broken.
Fortunately, Jan Karel has an exhaustive write-up on how to fix and prevent this problem. Go to FixLinks2UDF.
John Walkenbach, in Power Utility Pak, copies the UDF to the workbook in which it lives. I think that’s perfect for a general purpose utility, but it goes against my rule of keeping code and layout separate. If I change the UDF, it won’t be changed everywhere. I could make the argument that it shouldn’t change for previously created reports, but I just don’t know.
In this add-in, I already capture the opening of reports, so I suppose I’ll just update the links as they’re opened. In any event, I hope this link love kicks JKP’s article higher on Google so I don’t have to search so hard for it next time.
I usually keep my UDFs in a workbook rather than an add-in, with some documentation and examples.
I keep the master file in my Spread folder, and copy the current version to my project folders as required. That way I have a copy of the version used on the actual project, rather than linking to a possibly updated version in a different folder, which may get moved or give different results.
In older versions of PUP, the UDFs were in XLA files. But that caused so many problems for users (e.g., sending a file to a colleague) that it made sense to change it so the functions traveled with the workbook.
Fortunately, those UDFs have been around a long time and are relatively problem-free.
Just thought I’d add an additional complication to this. Sometimes, if the UDF lies within a circular calculation chain and the link goes haywire, the circular calculation chain gets stuck with a #Name error even after the links are fixed. The only workable solution I’ve found is to port all the formulas for each erroring UDF cell into a temporary holder (I use an ado recordset), then write each one back and force a full recalc.
Robin:
With any circular calculation scheme in Excel I advise you to ensure you can break the circle(s) by using a reset cell and a cell with a sensible starting value for the circle.
Say we have cell A1 as the reset cell and B1 as the cell that holds a starting value, then make sure one of the cells in the circular calculation model has something like:
=IF(A1=”RESET”,B1,OriginalFormulaInCell)
Dick:
http://www.dailydoseofexcel.com/archives/2008/06/02/fixing-links-to-udfs-in-addins/