In version 6 of XLL+, a unified event model has been introduced. This model makes it straightforward to catch interesting events in the life of Excel, and react to them. Some of these events are listed below:

XllOpen

The XLL itself is being opened by Excel. This is a good time to do initialization and authorization.

XllClose

The XLL itself is being closed. This is the time to close and release resources used by the XLL.

NewWorkbook

A new workbook has been created. If there is code you wish to run against all new workbooks, do it here.

WorkbookOpen

An existing workbook has been opened. This is a good time to check the state of a woprkbook, and perhaps to update data that is controlled by states outside the workbook itself.

Calculate

A worksheet has been recalculated. This is often a good time to deal with external data, such as that represented by handles.

Observer pattern

The event model uses the standard Observer pattern. An instance of an observer class is created, and it is attached to the event using CXlEvent::Register. While the observer is attached, its virtual method Update will be called whenever the event occurs.

The observer can be disconnected from the event by calling CXlEvent::Unregister.

Event arguments

Different events have different event data associated with them. For example, the WorkbookOpen event passes the name of the workbook, while the WorksheetChanged event passes a range on a worksheet.

The base observer class is implemented as a template class, and the type of event argument is used as a parameter to the class.

template<class EventArgsType>
class CXlEventObserver {
public:
    CXlEventObserver() { }
    virtual void Update(EventArgsType* e)=0;
};

So, for example, observers of the WorkbookOpen event are of type CXlEventObserver<CXlWorkbookEventArgs>. Whenever the event occurs, an argument of type CXlWorkbookEventArgs is passed to the observer's Update method.

See the class reference for each of the events for details of the appropriate event argument class.

Coding event observers

Here is an example of observing the Calculate event:

class CMyCalculateObserver : public CXlEventObserver<CXlWorksheetEventArgs> {
protected:
    virtual void Update(CXlWorksheetEventArgs* e)
    {
        // Do something
        TRACE("Worksheet %s has been recalculated\n", 
            (LPCSTR)e->GetSheetName());
    }
};
CMyCalculateObserver calculateObserver;

// Register the observer
CXllApp::Instance()->CalculateEvent().Register(&calculateObserver);

// Unregister the observer
CXllApp::Instance()->CalculateEvent().Unregister(&calculateObserver);

The steps are:

1. Create an observer class, derived from CXlEventObserver<T>, where T is the type of the event's arguments.
2. In the observer class's Update method, put the code that reacts to the event.
3. Create an instance of the observer class.
4. Connect the observer to the event by calling CXlEvent::Register.

Static observers

You can skip much of the work by using a static observer class. If you derive your observer class from a static observer base class, then it will automatically be registered at the appropriate time and unregistered when the XLL closes.

class CMyCalculateObserver2 : public CXlCalculateEventStaticObserver {
protected:
    virtual void Update(CXlWorksheetEventArgs* e)
    {
        // Do something
        TRACE("Worksheet %s has been recalculated\n", 
            (LPCSTR)e->GetSheetName());
    }
};
CMyCalculateObserver2 m_calculateObserver2;

In this case, the instance m_calculateObserver2 does not need to be registered. It will be done automatically when the add-in is opened, and the observer will be unregistered when the add-in is closed.

You can find a list of static observer classes in the Class Reference.

Next: XllOpen & XllClose events >>