Fixing Links To UDFs in Addins

Pages in this article

Detecting Workbook Opening

First of all, we need a way to detect if the user has opened a workbook. This may be done using a class module. This class module will contain a Workbook_Open event routine that will fire when the user opens a file in Excel. Setting up such an "event listener" contraption takes a couple of steps.

Creating The Class Module

Insert a new class module in your project (Insert, Class Module). Make sure you change its name as indicated in the screenshot below.

Change the name of the class module in its properties

In this newly created class, insert these lines of code:

'-------------------------------------------------------------------------
' Module    : cAppEvents
' Company   : JKP Application Development Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Handles Excel Application events
'-------------------------------------------------------------------------
Option Explicit

'This object variable will hold the object who's events we want to respond to
'Note the "WithEvents" keyword, which is what we need to tell VBA it is an object
'with events.
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'-------------------------------------------------------------------------
' Procedure : App_WorkbookOpen Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2008
' Author    : Jan Karel Pieterse
' Created   : 2-6-2008
' Purpose   : Runs code to redirect UDFs to this workbook
'-------------------------------------------------------------------------
    ProcessNewBookOpened Wb
End Sub

Private Sub Class_Terminate()
    Set App = Nothing
End Sub

Let's break this up in pieces and explain what is going on.

Public WithEvents App As Application

This is a declaration line. It declares an Object variable called "App" of type "Application" (this is Excel). The WithEvents keyword tells Visual Basic that you want it to "listen" to events (consider events as being times set on your alarm clock: if a time passes, your alarm clock makes a sound).

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Here we have setup an event listener for the Workbook_Open event.

Tip: You can find out what events are available to you by using the dropdowns at the top of the code window:

The events dropdown at the top of the code window

As soon as you select an event on the right-hand side, the editor will automatically insert the skeleton code needed for this event.

ProcessNewBookOpened Wb

This line calls the routine "ProcessNewBookOpened" to handle the opening of the file. Conveniently, VBA has provided you with an object variable that holds a reference to the newly opened workbook: "Wb". This variable is passed along to the processing routine so it knows which workbook to work with.

Now that that has been setup, you have to tell VBA to use this new class. Insert a normal module (Insert, module) and call the module modInit (not necessary, but naming your modules is a good habit).

Insert this code into the new module:

Option Explicit

'Create a module level object variable that will keep the instance of the
'event listener in memory (and hence alive)
Dim moAppEventHandler As cAppEvents

Sub InitApp()
    'Create a new instance of cAppEvents class
    Set moAppEventHandler = New cAppEvents
    With moAppEventHandler
        'Tell it to listen to Excel's events
        Set .App = Application
    End With
End Sub

What happens here is mostly explained in the green comment lines in the code shown above. No need to repeat that here.

Next: The processing code