Fixing Links To UDFs in Addins

Pages in this article

1. Fix #Name! Errors
2. Detecting Workbook Opening
3. Processing Newly Opened Workbook
4. Handle Workbooks Opened From Explorer

Methods to Fix #Name! Errors

Use fixed location

Of course the simplest way to avoid the problem is by fixing the location of your addin. Tell all your users where the addin should be installed (or even better: create a setup tool that doesn't allow it to be installed elsewhere). Your #Name! errors will not resurface.

Don't use an addin

Well, not exactly so, you could still have an addin. But instead of keeping your UDF code inside the addin, you create a facility that copies the UDF routine into each workbook that uses it.

This is a neat solution, but it requires that your user has the security option "Trust Access to Visual Basic Project" set. John Walkenbach's Power Utility Pack used to use this trick.

Redirect the UDFs to the new location

This is the technique I'll describe extensively in this article. The addin checks each workbook the user opens whether this new workbook contains a link to the addin. If so, it ensures the link points to the proper location.