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
    '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"
                'We've done this 20 times, do not schedule another
                'and reset the counter
                TimesLooped = 0
            End If
            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
    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


download the latest source code from github


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!



All comments about this page:

Comment by: hymced (22-11-2016 16:50:38) deeplink to this comment


very interresting article !
just one thing : I can't get the point of the Workbook_BeforeClose and Workbook_Deactivate events on the ThisWorkbook VB component of the add-in ??

Thank you for your answer !

Comment by: Jan Karel Pieterse (22-11-2016 19:19:11) deeplink to this comment

Hi hymced,

Those are there to avoind the add-in loosing the application events when:

- The user quits Excel (now the BeforeClose event fires, shutting down the application event handler)
- Excel asks to save changes and the user cancels the save and hence the quit of Excel

So the code schedules a macro that reinstates the event handler, but when the deactivate event triggers (just before the add-in closes) it unschedules that same event again to avoid the ontime macro forcing the add-in to be opened again.
Clear as mud?

Comment by: hymced (23-11-2016 14:29:30) deeplink to this comment

Yes, after several reading, I do get it ! ^^

Here how I would put your explanation another way round :

These _BeforeClose and _Deactivate events are there to avoid the add-in loosing the application events when the user quits Excel (the BeforeClose event fires, shutting down the application event handler) but then cancels the save asked by Excel if there are changes (and hence the quit of Excel).

Indeed, the code schedules a macro that reinstates the event handler after the _BeforeClose event, but when the _Deactivate event triggers (just before the add-in closes) it unschedules that same event again to avoid the ontime macro forcing the add-in to be opened again.

Slightly different I agree :)

Anyway, thank you for your quick reply ;)

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:

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