Fixing Links To UDFs in Addins

Introduction

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). UDFs typically are placed in addins. As long as the addin is installed, the UDFs 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:

='C:\data\FixLink2UDF.xla'!UDFDemo(A1,A2)

This article describes a couple of ways to avoid/resolve this situation.

Read on...

Contents

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