1

Fixing Links To = UDFs in Add-ins

Jan Karel Pieterse is a self-employed Excel developer and the owner of JKP Application Development Services, founded in 2003. He has over 20 years of corporate business experience and is an export in Excel and VBA. Visit his website at http://www.jkp-ads.com for informatio= n on his services, many in-depth articles on Excel and for free utilities.

Introduction<= o:p>

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 add-ins. As lon= g as the add-in is installed, the UDFs work as expected. You get into trouble wh= en the location of the add-in changes, for example because you have distributed the Add-in to your co-workers and they have installed it to their local user add-in 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 add-in 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:

=3D'C:\data\FixL= ink2UDF.xla'!UDFDemo(A1,A2)

This article des= cribes a couple of ways to avoid/resolve this situation.

Methods to Fix #Name! Errors

Use fixed location=

Of course the si= mplest way to avoid the problem is by fixing the location of your add-in. Tell all your users where the add-in should be installed (or even better: create a s= etup tool that doesn't allow it to be installed elsewhere). Your #Name! errors w= ill not resurface.

Don't use an add-in

Well, not exactl= y so, you could still have an add-in. But instead of keeping your UDF code inside= the add-in, you create a facility that copies the UDF routine into each workbook that uses it.

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

Redirect the = UDFs to the new location

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

Detecting Workbook Open= ing

First of all, we= need a way to detect if the user has opened a workbook. This may be done using a class module. This class module will contain a Workbook_Open event routine = that will fire when the user opens a file in Excel. Setting up such an "eve= nt listener" contraption takes a couple of steps.

Creating The = Class Module

Insert a new cla= ss module in your project (Insert, Class Module). Make sure you change its nam= e as indicated in the screenshot below.

In this newly cr= eated class, insert these lines of code:

'-------------------------------------------------------------------' Module    : cAppEvents
' Company   : JKP Application Devel= opment Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Handles Excel Applica= tion events
'--------------------------------------------= -----------------------Option Explici= t

'This object variable will hold the object wh= o's events we want to respond to
'Note the "WithEvents" keyword, which is what we need to tell VBA= it is an object
'with events.
Public WithEvents App As Application

Private = Sub App_WorkbookOpen(ByVal Wb As Workbook)
'--------------------------------------------= -----------------------
' Procedure : App_WorkbookOpen Created by Jan Karel Pieterse
' Company   : JKP Application Devel= opment Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Runs code to redirect= UDFs to this workbook
'--------------------------------------------= -----------------------
    ProcessNewBookOpened Wb
End Sub<= /span>

Private = Sub Class_Terminate()
    Set App =3D Nothing
End Sub<= /span>

Let's break this= up in pieces and explain what is going on.

Public WithEvents App As Application

This is a declar= ation line. It declares an Object variable called "App" of type "Application" (this is Excel). The WithEvents keyword tells Visual Basic that you want it to "listen" to events (consider events as being times set on your alarm clock: if a time passes, your alarm clock mak= es a sound).

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Here we have set= up an event listener for the Workbook_Open event.

Tip: You can fin= d out what events are available to you by using the dropdowns at the top of the c= ode window:

As soon as you s= elect an event on the right-hand side, the editor will automatically insert the skeleton code needed for this event.

&= nbsp; ProcessNewBookOpened Wb

This line calls = the routine "ProcessNewBookOpened" to handle the opening of the file. Conveniently, VBA has provided you with an object variable that holds a reference to the newly opened workbook: "Wb". This variable is pa= ssed along to the processing routine so it knows which workbook to work with.

Now that that ha= s been setup, you have to tell VBA to use this new class. Insert a normal module (Insert, module) and call the module modInit (not necessary, but naming your modules is a good habit).

Insert this code= into the new module:

Option Explicit

'Create a module level object variable that w= ill keep the instance of the
'event listener in memory (and hence alive)
Dim moAppEventHandler As cAppEvents

Sub InitApp()
    'Create a new instanc= e of cAppEvents class
    Set moAppEventHandler =3D New cAppEvents     With moAppEventHandler
        'Tell it to listen to Excel's events
        Set .App =3D Application
    End With
End Sub<= /span>

What happens her= e is mostly explained in the green comment lines in the code shown above. No nee= d to repeat that here.

Processing the Newly Opened Workbook

Once the add-in = has detected that the user opened a new workbook some action has to be taken.

First of all, we= 'll check all external links of the workbook to see if any point to a file whose name resembles our add-in. After that -to be on the safe side- we also check all formulas which use our UDF(s) and update them so they point to our add-= in.

The code shown b= elow is part of a "normal" module called modProcessWBOpen:<= /span>

Sub ProcessNewBookOpened(= oBk As Workbook)
‘--------------------------------------= -----------------------------
' Procedure : ProcessNewBookOpened
' Company   : JKP Application Devel= opment Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : When a new workbook is opened, this sub will be run.
' Called from: clsAppEvents.App_Workbook_Open= and ThisWorkbook.Workbook_Open
‘--------------------------------------= -----------------------------
'Sometimes OBk is nothing?
    If oBk Is Nothing Then Exit Sub
    If oBk Is ThisWorkbook Then Exit Sub=
    If oBk.IsInpla= ce Then Exit Sub
    CheckAndFixLinks oBk
    ReplaceMyFunctions oBk
    CountBooks
End Sub<= /span>

Sub CheckAndFixLinks(oBook As Workbook)
‘--------------------------------------= -----------------------------
' Procedure : CheckAndFixLinks Created by Jan= Karel Pieterse
' Company   : JKP Application Devel= opment Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Checks for links to a= dd-in and fixes them
'       &n= bsp;     if they are not pointing to proper location
‘--------------------------------------= -----------------------------
    Dim vLink As Variant
    Dim vLinks As Variant
    'Get all links
    vLinks =3D oBook.LinkSources(xlExcelLinks)
    'Check if we have any link= s, if not, exit
    If IsEmpty(vLi= nks) Then Exit Sub
    For Each vLink In<= /span> vLinks
        If vLink Like "*" & ThisWorkbook.Name Then
       &nb= sp;    'We've found a link to our add-in, redirect it to
       &nb= sp;    'its current location. Avoid prompts
            App= lication.DisplayAlerts =3D False
            oBo= ok.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
            App= lication.DisplayAlerts =3D True
        End If
    Next
    On Error GoTo 0
End Sub<= /span>

Private = Sub ReplaceMyFunctions(oBk As Workbook)
‘--------------------------------------= -----------------------------
' Procedure : ReplaceMyFunctions Created by J= an Karel Pieterse
' Company   : JKP Application Devel= opment Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Ensures My functions = point to this add-in
‘--------------------------------------= -----------------------------
    Dim oSh As Worksheet
    Dim oFirstFoun= d As Range
    Dim oFound As Range

    On Error Resume Next
    'Search through all s= heets looking for the UDF "UDFDemo("
    For Each oSh In oBk.Worksheets
        Set oFirstFound =3D _
        oSh.UsedRange.Cells.Find(wh= at:=3D"UDFDemo(", after:=3DoSh.UsedRange.Cells(1, 1), _
            &nb= sp;            =         LookIn:=3DxlFormulas, LookAt:=3DxlPart, _
            &nb= sp;            =         SearchOrder:=3DxlByRows, SearchDirection:=3DxlNext, MatchCase:=3DFalse)
        If Not oFirstFound Is Nothing = Then
            'Found one, change the formula (prepend with path t= o me)
            'We assume the function is on its own, NOT nested i= nside another!!!
            oFi= rstFound.Formula =3D "=3D'" & ThisWorkbook.FullName & "'!" &= _
            &nb= sp;            =          Right(oFirstFound.For= mula, _
            &nb= sp;            =             &nb= sp;  Len(oFirstFound.Formula) - _
            &nb= sp;            =             &nb= sp;  InStr(oFirstFound.Formula, "My(") + 1)
            Set oFound =3D oFirstFound
            Do
            &nb= sp;   Set oFound =3D _
            &nb= sp;   oSh.UsedRange.Cells.Find(what:=3D"UDFDemo(", after:=3DoFound, LookIn:=3DxlFormulas, _
            &nb= sp;            =             &nb= sp;   LookAt:=3DxlPart, SearchOrder:=3DxlByRows, _
            &nb= sp;            =             &nb= sp;   SearchDirection:=3DxlNext, MatchCase:=3DFalse)
            &nb= sp;   If Not = oFound Is Nothing Then
            &nb= sp;       'We assume the function is on its own, NOT nested i= nside another!!!
            &nb= sp;       oFound.Formula =3D "=3D'" & ThisWorkbook.FullName & "'!" &= _
            &nb= sp;            =             Right(oFound.Formula, Len(oFound.Formula) - _
            &nb= sp;            =             &nb= sp;            =          InStr(oFound.Formula, "My(") + 1)
            &nb= sp;   End If<= br>             Loop Until oFound Is Nothing Or oFound.Address =3D oFirstFound.Addr= ess
        End If
    Next
End Sub<= /span>

Check out the co= mments in the code to find out what is going on.

Well, that shoul= d be all there is to it, right? Not so. Sometimes when one double clicks a file = in Explorer, Excel has already opened that file BEFORE your add-in is fully lo= aded and initialized.

Handle Workbo= oks Opened From Explorer

The trick I used= here is to schedule a macro when the add-in opens, which detects if any workbooks have to be processed. This is done using these lines in the Workbook_Open e= vent of the add-in:

  &= nbsp; modProcessWBOpen.TimesLooped =3D 0
    'Schedule macro to run after initialisation of Excel has fully been done.
    'Sometimes, the add-in hasn't fully been initialized and the
    'workbook we want che= cked is opened BEFORE we have fully initialized the
    'add-in.
    'This may happen when= one double clicks a file in explorer
    Application.OnTime Now + TimeValue("00:00:03"), "CheckIfBookOpened"
End Sub<= /span>

I've set this up= in such a way, that I count the number of times the routine called "CheckIfBookOpened" is run. After 20 times I suspend looking for = new files, assuming there are none. Any new files opened will be handled by the class module.

Here is all the = code that belongs to checkIfBookOpened (placed in the same module "modProcessWBOpen"). Note that I have added two module-level variables at the top of that module:

Option Explicit

'Counter to keep score of how many workbooks = are open
Dim mlBookCount As Long

'Counter to check how many times we've looped=
Private mlTimesLooped As Long=

The remainder= of the code is:

Sub CheckIfBookOpened() ‘--------------------------------------= -----------------------------
' Procedure : CheckIfBookOpened
' Company   : JKP Application Devel= opment Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 6-6-2008
' Purpose   : Checks if a new workb= ook has been opened (repeatedly until activeworkbook is not nothing)
‘--------------------------------------= -----------------------------
    'First, we check if t= he number of workbooks has changed
    If BookAdded <= span style=3D'color:#00007F'>Then
        If ActiveWorkbook Is Nothing Then
            mlB= ookCount =3D 0
            'Increment the loop counter
            Tim= esLooped =3D TimesLooped + 1
            'May be needed if Excel is opened from Internet exp= lorer
            App= lication.Visible =3D True
            If TimesLooped < 20 Then
                'We've not yet done this 20 times, schedule another= in 1 sec
            &nb= sp;   Application.OnTime Now + TimeValue("00:00:01"), "CheckIfBookOpened"
            Else
                'We've done this 20 times, do not schedule another =
            &nb= sp;   'and reset the counter
            &nb= sp;   TimesLooped =3D 0
            End If<= br>         Else
            Pro= cessNewBookOpened ActiveWorkbook
        End If
    End If
End Sub<= /span>

Public P= roperty Get TimesLooped() As Long
    TimesLooped =3D mlTimesLooped
End Prop= erty

Public P= roperty Let TimesLooped(ByVal lTimesLooped As Long)
    mlTimesLooped =3D lTimesLooped
End Prop= erty

Function BookAdded() As Boolean
    If mlBookCount <> Workbooks.Count Then
        BookAdded =3D True
        CountBooks
    End If
End Func= tion

Basically what h= appens is this:

When the add-in opens, we count how many workbooks are currently open in Excel
We schedule the macro CheckIfBookOpened to be run in a couple of seconds, giving Excel (and = our add-in too!) the opportunity to fully initialize. In the mean time, Ex= cel may also load the file we've double clicked upon.
CheckIfBookOpened then ch= ecks if the number of open workbooks has changed and if so, runs the "ProcessNewBookOpened" routine on the active workbook

Download the samp= le file

Conclusion

That's it. A bit convoluted, I agree. The other two options I noted at the start of this art= icle may be simpler for your situation.

Comments or suggestions for this article? Please = send= us your feedback!