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.
'-----------------------------------------------------------
' 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 (1/30/2011 7:50:56 PM)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 (1/30/2011 7:54:53 PM)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 (1/30/2011 10:10:25 PM)Hi Matt,
You're right of course, thanks for your comment!
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.