Fixing Links To UDFs in Addins

Pages in this article

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, this is normally not needed) 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
    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

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 (21-1-2009 14:54:08) deeplink to this comment

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
     For Each oSh In oBk.Worksheets
         Dim oFirstFound As Range
         Dim lWorkbookName As String
         Dim lWorkBookNameLength As Long
         Dim oFound As Range
         Dim lCondition As Boolean
         lWorkbookName = ThisWorkbook.Name & "'!"
         lWorkBookNameLength = Len(lWorkbookName)
         On Error Resume Next
         Set oFirstFound = oSh.Cells.Find(What:=lWorkbookName, LookIn:=xlFormulas, LookAt:=xlPart, _
                                          SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
         'On Error GoTo 0
         If Not oFirstFound Is Nothing Then
             Set oFound = oFirstFound
             lCondition = True
             Debug.Assert False
             'Find all the cells containing references to the UDF
             Do
                 Dim vFormula As Variant
                 Dim lPos1, lPos2 As Long
                 'Replace all references to the UDF from the formula
                 vFormula = oFound.Formula
                 lPos2 = InStr(vFormula, lWorkbookName)
                 Do While lPos2 > 0
                     lPos1 = InStrRev(vFormula, "'", InStr(lPos2, vFormula, ThisWorkbook.Name))
                     lPos2 = lPos2 + lWorkBookNameLength
                     vFormula = Left(vFormula, lPos1 - 1) & Right(vFormula, Len(vFormula) - lPos2 + 1)
                     lPos2 = InStr(vFormula, lWorkbookName)
                 Loop
                 oFound.Formula = vFormula
                 Set oFound = oSh.UsedRange.Cells.FindNext(After:=oFound)
                 If (oFound Is Nothing) Then
                     lCondition = False
                 ElseIf (oFound.Address = oFirstFound.Address) Then
                     lCondition = False
                 End If
             Loop While lCondition
         End If
     Next oSh
End Sub

Comment by: Matt Carter (30-1-2011 19:50:56) deeplink to this comment

Many thanks for this code which has fixed a problem I have struggled with for years.

A suggested improvement is to use this code when checking for links to your workbook:

If vLink Like "*" & ThisWorkbook.Name And vLink <> ThisWorkbook.FullName Then

I found that this makes it a bit quicker to open large workbooks. I'm assuming that previously even though the link redirect was to the same path as the current link path it still meant the workbook needed to recalculate.

So far I havent found the need to execute the second routine: ReplaceMyFunctions. The link redirect seems to do it automatically.

Comment by: Matt Carter (30-1-2011 19:54:53) deeplink to this comment

This comment is really for the previous page but I found that the routine InitApp needs to be executed from Private Sub Workbook_Open() in the ThisWorkbook object of the add-in. I suspect for an expert this would have been obvious but for dabblers like me it would be clearer for the explanation to explicitly state this.

Once again thanks for the code!

Comment by: Jan Karel Pieterse (30-1-2011 22:10:25) deeplink to this comment

Hi Matt,

You're right of course, thanks for your comment!

Comment by: Britt (30-8-2012 20:36:34) deeplink to this comment

I was directed to this as a possible solution to a problem I was having. I've been trying to implement it, but VBA is complaining that "CountBooks" is undefined. Since I'm not sure what function "CountBooks" is trying to accomplish I don't have any idea how to fix the problem.

Thanks.

Comment by: Jan Karel Pieterse (31-8-2012 11:36:48) deeplink to this comment

Hi Britt,

I apologise, the CountBooks routine is missing from the code I published on these pages. Here it is:

Sub CountBooks()
mlBookCount = Workbooks.Count
End Sub

The complete code can be found in the download available on the next page of this article.

Comment by: LeHerb (11-12-2012 09:42:06) deeplink to this comment

Thanks very much for posting this solution.

I developed an Excel Addin some time ago. At the time of development we had German XP clients and I therefore placed the addin under C:\Programme\... We changed to an international version in the meantime and the Addin should be relocated to C:\Program Files\...
There are countless sheets out there all with links to the C:\Programme version and they all have to be adapted. With the help of your code I'm optimistic to get this done without bothering the clients.

Comment by: Jan Karel Pieterse (11-12-2012 09:52:45) deeplink to this comment

Hi LeHerb,

I'm pleased to hear my article has helped you serve your clients!

Comment by: Klaus S. (29-4-2014 14:18:39) deeplink to this comment

Hi,

I really loved the ReplaceMyFunctions function in Excel 2007.
Recently we switched to Excel 2010, and it seems that with the new version of Excel the default formulas of Tables (not Sheets) are not replaced anymore.
I know the default formula is defined inside table1.xml as the element "calculatedColumnFormula" of the element "tableColumn".
But so far I didn't find any way to access this element using VBA.
Do you have any ideas on how this could be fixed?

Comment by: Jan Karel Pieterse (8-5-2014 07:49:08) deeplink to this comment

Hi Klaus,

The function does not sound familiar, where do I find it?

Comment by: Martin Zlamal (Mojin) (18-1-2016 10:19:35) deeplink to this comment

Hi Jan,
thanks for such a great guidance!

My final solution risks, that the UDF's full path always begins with apostrophe (') and ends with exclamation (!) – for example ='c:\path\add-in.xlam'!MyUDF(arg1). So the scripts searches cells for add-in's name, then finds the last apostrophe (in the left from found add-in's name) and finds the next exclamation (in the right) and replaces it all between with nothing. This should solve an eventual functions nesting.

Moreover, as I really like matrixes, the script should take into account the array formulas:
If oFound.HasArray Then 'check if the formula is part of a matrix
oFound.FormulaArray = Replace(oFound.Formula, AddinPath, "")
Else
oFound.Formula = Replace(oFound.Formula, AddinPath, "")
End If

Once again thank you for a very helpful article!

Comment by: Jan Karel Pieterse (18-1-2016 10:32:51) deeplink to this comment

Hi Martin,

Thanks!

Comment by: Jim Webb (13-6-2017 05:55:24) deeplink to this comment

Hi Martin Zlamal,

Yes, and if in Replace you split the formula string into a first part (containing the path you want to change) and a second part (rest of the string) and call the Replace function recursively on the second part, it will easily cope with multiple instances of the UDF path.

Comment by: Ivor Shaer (23-5-2018 04:19:54) deeplink to this comment

I need assistance please.

I use an userform to open workbooks and once the vba code has opened the required workbook it must (1) activate the workbook opened(2) activate a specified/named worksheet.

However, the code

Windows(RequiredFile).Activate

gives an error message '9' Subscript out of range.

I then click "debug" and F5 and the macro runs perfectly to completion.

The code for opening the required workbook is definitely.
correct as I can see the opened workbook.

It thought that possibly the vba runs too fast so I put in a wait code. This did not help.

I also attempted a loop where if the code did not recognise that the file was open

If workbookopen(RequiredFile) = False

then it would open it again. This led to an infinite loop.

Do you have a solution for me please?

Comment by: Jan Karel Pieterse (23-5-2018 10:47:21) deeplink to this comment

Hi Ivor,

Can you please show me the relevant piece of code?
NB: I have edited your message to include the closing /VB tag after each VBA line

Comment by: Josep (22-10-2020 11:38:00) deeplink to this comment

Hi Jan,
Tank you for your workaround, it has been really helpful to me for a while.

However, now I am having a problem with protected worksheets. So I wonder if there could be a solution.

The worksheet is protected, but the cells containing the UDF are not. Once the file is opened I'm getting an error on the following line:

oBook.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks

The error (translated from spanish): Run-time error '1004'. The command could not be run in a protected sheet. To use this command unprotect first the sheet (...).

Any advice from your side would be appreciated.
Best regards,

Comment by: Jan Karel Pieterse (22-10-2020 14:35:00) deeplink to this comment

Hi Joseph,

I advise to add code that first unprotects all sheets and afterwards, protects all sheets again.

Comment by: Robert Hudson (28-2-2022 16:03:00) deeplink to this comment

I've been pulling my hair out over this problem. I am immensely grateful for your sharing this code. I had a little trouble until you reminded me to add the following code to ThisWorkbook in the .xlam:

Private Sub Workbook_Open()
Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!InitApp"
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.