VBA Add-Ins (XLA, XLAM) that provide User Defined Functions (UDF) are an easy way to add functionality to Microsoft Excel. But a typical problem with such add-ins result from the way Excel stores function calls in a workbook. Your UDFs work fine as long as the path to the addin does not change.

If you open a workbook, all cells that use a UDF display a #NAME! error if the add-in that provides these UDFs is not installed. The formula of such a cell shows the full and absolute path to the add-in that was used. And the #NAME! error also appears if the add-in is installed but the path to the add-in has changed.

This is because Excel stores the links to other workbooks with complete path. These links are saved with your workbook, and Excel can update the links when the workbook is re-opened. A VBA add-in (xla, xlam) is just a special kind of workbook. Therefore, Excel stores the complete path if a user defined function resides in a VBA add-in.

='C:\my addins\sample.xla'!sampleUDF(A1,A2)

This is a typical problem if you share add-ins with co-workers where the add-ins are installed in the local user’s addin folder. In this case, every user of the add-ins will have a different add-in path.

But this behavior can cause errors even if you don’t share VBA add-ins. You only need to use functions of the Excel Analysis ToolPak and share such a workbook with others who use another version of Microsoft Office.

='C:\Program Files\MS Office\Office11\Library\ATPVBAEN.XLA'!yearfrac(A1,A2)

This problem is solved in Excel 14 where the Analysis ToolPak still exists but the functions have become native worksheet functions.

There are several ways to avoid the problem. You could include your user defined functions in your workbooks or define a fixed location for addins. If you’re not limited to VBA, you can use Automation Add-Ins or XLL Add-Ins for your UDFs. The next article will describe a VBA solution.