Fixing Links To UDFs in Addins

Pages in this article

1. Fix #Name! Errors
2. Detecting Workbook Opening
3. Processing Newly Opened Workbook
4. Handle Workbooks Opened From Explorer

Handle Workbooks 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 event of the add-in:

    modProcessWBOpen.TimesLooped = 0
    'Schedule macro to run after initialisation of Excel has fully been done.
    'Sometimes, the addin hasn't fully been initialised and the
    'workbook we want checked is opened BEFORE we have fully initialised the
    'addin.
    'This may happen when one double clicks a file in explorer
    Application.OnTime Now + TimeValue("00:00:03"), "CheckIfBookOpened"

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 Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 6-6-2008
' Purpose   : Checks if a new workbook has been opened (repeatedly until activeworkbook is not nothing)
'-------------------------------------------------------------------------
    'First, we check if the number of workbooks has changed
    If BookAdded Then
        If ActiveWorkbook Is Nothing Then
            mlBookCount = 0
            'Increment the loop counter
            TimesLooped = TimesLooped + 1
            'May be needed if Excel is opened from Internet explorer
            Application.Visible = True
            If TimesLooped < 20 Then
                'We've not yet done this 20 times, schedule another in 1 sec
                Application.OnTime Now + TimeValue("00:00:01"), "CheckIfBookOpened"
            Else
                'We've done this 20 times, do not schedule another
                'and reset the counter
                TimesLooped = 0
            End If
        Else
            ProcessNewBookOpened ActiveWorkbook
        End If
    End If
End Sub

Public Property Get TimesLooped() As Long
    TimesLooped = mlTimesLooped
End Property

Public Property Let TimesLooped(ByVal lTimesLooped As Long)
    mlTimesLooped = lTimesLooped
End Property

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

Basically what happens 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 addin too!) the opportunity to fully initialise. In the mean time, Excel may also load the file we've double clicked upon.
• CheckIfBookOpened then checks if the number of open workbooks has changed and if so, runs the "ProcessNewBookOpened" routine on the active workbook

Download the sample file

or

download the latest source code from github

Conclusion

That's it. A bit convoluted, I agree. The other two options I noted at the start of this article may be simpler for your situation. Should you have an even simpler one, don't hesitate to leave a comment!