Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF).
UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s work as expected. You get into trouble when the location of the addin changes, for example because you have distributed the Addin to your co-workers and they have installed it to their local user addin folder (which is different for each user!).
As soon as a file which uses the UDF is opened on a system that uses a different addin path, all cells that use your UDF show #NAME! errors. Also, the complete path to the original location of the add-in is shown in each formula:
There are a couple of ways to avoid/resolve this situation:
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.
Walkenbach’s Power Utility Pack uses this trick.
Redirect the UDF’s 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.
Jan Karel Pieterse