Fixing Links To UDFs in Addins

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 who's 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 below is part of a "normal" module called modProcessWBOpen:

Sub ProcessNewBookOpened(oBk As Workbook)
'-------------------------------------------------------------------------
' Procedure : ProcessNewBookOpened
' Company   : JKP Application Development 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.IsInplace Then Exit Sub
    CheckAndFixLinks oBk
    ReplaceMyFunctions oBk
    CountBooks
End Sub

Sub CheckAndFixLinks(oBook As Workbook)
'-------------------------------------------------------------------------
' Procedure : CheckAndFixLinks Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Checks for links to addin and fixes them
'             if they are not pointing to proper location
'-------------------------------------------------------------------------
    Dim vLink As Variant
    Dim vLinks As Variant
    'Get all links
    vLinks = oBook.LinkSources(xlExcelLinks)
    'Check if we have any links, if not, exit
    If IsEmpty(vLinks) Then Exit Sub
    For Each vLink In vLinks
        If vLink Like "*" & ThisWorkbook.Name Then
            'We've found a link to our add-in, redirect it to
            'its current location. Avoid prompts
            Application.DisplayAlerts = False
            oBook.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
            Application.DisplayAlerts = True
        End If
    Next
    On Error GoTo 0
End Sub

Private Sub ReplaceMyFunctions(oBk As Workbook)
'-------------------------------------------------------------------------
' Procedure : ReplaceMyFunctions Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Ensures My functions point to this addin
'-------------------------------------------------------------------------
    Dim oSh As Worksheet
    Dim oFirstFound As Range
    Dim oFound As Range

    On Error Resume Next
    'Search through all sheets looking for the UDF "UDFDemo("
    For Each oSh In oBk.Worksheets
        Set oFirstFound = _
        oSh.UsedRange.Cells.Find(what:="UDFDemo(", after:=oSh.UsedRange.Cells(1, 1), _
                                 LookIn:=xlFormulas, LookAt:=xlPart, _
                                 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not oFirstFound Is Nothing Then
            'Found one, change the formula (prepend with path to me)
            'We assume the function is on its own, NOT nested inside another!!!
            oFirstFound.Formula = "='" & ThisWorkbook.FullName & "'!" & _
                                  Right(oFirstFound.Formula, _
                                        Len(oFirstFound.Formula) - _
                                        InStr(oFirstFound.Formula, "My(") + 1)
            Set oFound = oFirstFound
            Do
                Set oFound = _
                oSh.UsedRange.Cells.Find(what:="UDFDemo(", after:=oFound, LookIn:=xlFormulas, _
                                         LookAt:=xlPart, SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, MatchCase:=False)
                If Not oFound Is Nothing Then
                    'We assume the function is on its own, NOT nested inside another!!!
                    oFound.Formula = "='" & ThisWorkbook.FullName & "'!" & _
                                     Right(oFound.Formula, Len(oFound.Formula) - _
                                                           InStr(oFound.Formula, "My(") + 1)
                End If
            Loop Until oFound Is Nothing Or oFound.Address = oFirstFound.Address
        End If
    Next
End Sub

Check out the comments in the code to find out what is going on.

Well, that should 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 loaded and initialised.

Next: How to handle workbooks opened from Explorer.

Comments

All comments about this page:

Comment by: Jorge Belenguer Faguas (1/21/2009 2:54:08 PM)

Here is my proposed code for the function "ReplaceMyFunctions".

Improvements:
- UDF can be nested inside another, can be located anywhere in the formula, and can appear many times in the same formula.
- Replaces not only "UDFDemo" but all functions related to the addin file.
- Performance has been improved.
Private Sub ReplaceMyFunctions(oBk As Workbook)
'-----------------------------------------------------------
' Procedure : ReplaceMyFunctions Created by Jan Karel Pieterse
' and Improved by Jorge Belenguer Faguas
' Company : JKP Application Development Services (c) 2008
' Author : Jan Karel Pieterse
' Created : 2-6-2008
' Modified : 1-21-2009 by Jorge Belenguer Faguas
' Purpose : Ensures My functions point to this addin
'-----------------------------------------------------------
Dim oSh As Worksheet
&nbsp&nbsp&nbsp&nbsp For Each oSh In oBk.Worksheets
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim oFirstFound As Range
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim lWorkbookName As String
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim lWorkBookNameLength As Long
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim oFound As Range
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim lCondition As Boolean
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lWorkbookName = ThisWorkbook.Name & "'!"
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lWorkBookNameLength = Len(lWorkbookName)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp On Error Resume Next
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Set oFirstFound = oSh.Cells.Find(What:=lWorkbookName, LookIn:=xlFormulas, LookAt:=xlPart, _
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 'On Error GoTo 0
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp If Not oFirstFound Is Nothing Then
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Set oFound = oFirstFound
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lCondition = True
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Debug.Assert False
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 'Find all the cells containing references to the UDF
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Do
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim vFormula As Variant
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Dim lPos1, lPos2 As Long
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 'Replace all references to the UDF from the formula
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp vFormula = oFound.Formula
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lPos2 = InStr(vFormula, lWorkbookName)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Do While lPos2 > 0
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lPos1 = InStrRev(vFormula, "'", InStr(lPos2, vFormula, ThisWorkbook.Name))
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lPos2 = lPos2 + lWorkBookNameLength
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp vFormula = Left(vFormula, lPos1 - 1) & Right(vFormula, Len(vFormula) - lPos2 + 1)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lPos2 = InStr(vFormula, lWorkbookName)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Loop
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp oFound.Formula = vFormula
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Set oFound = oSh.UsedRange.Cells.FindNext(After:=oFound)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp If (oFound Is Nothing) Then
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lCondition = False
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ElseIf (oFound.Address = oFirstFound.Address) Then
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp lCondition = False
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp End If
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Loop While lCondition
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp End If
&nbsp&nbsp&nbsp&nbsp Next oSh
End Sub

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.