Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Fix Links to UDFs > Processing Newly Opened Workbook
Deze pagina in het Nederlands

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

Showing last 8 comments of 13 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (8/31/2012 11:36:48 AM)

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 (12/11/2012 9:42:06 AM)

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 (12/11/2012 9:52:45 AM)

Hi LeHerb,

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

 


Comment by: Klaus S. (4/29/2014 2:18:39 PM)

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 (5/8/2014 7:49:08 AM)

Hi Klaus,

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

 


Comment by: Martin Zlamal (Mojin) (1/18/2016 10:19:35 AM)

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 (1/18/2016 10:32:51 AM)

Hi Martin,

Thanks!

 


Comment by: Jim Webb (6/13/2017 5:55:24 AM)

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.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].