Skip to main content

Getting Excel Events in Python

Tony Roberts
Updated

Excel raises events to let application developers know when certain things happen. For example, whenever a cell is changed, a Worksheet.Change event is raised.

If you have ever done any VBA, you may be familiar with these events as you can register event handlers that get called whenever the events are raised. These events aren't specific to VBA however. Excel uses a Microsoft technology called COM (sometimes called Automation) which exposes the entire Excel API to all COM enabled langugages, including Events. Python has excellent COM support via the win32com module contained within the pywin32 package. PyXLL provides a convenience function for getting the COM Excel.Application object, pyxll.xl_app().

The Excel events are described in Microsoft's Excel Object Model documentation.

To subscribe to COM events in Python we use the win32com.client.DispatchWithEvents function. This takes a COM object and a Python class that implements the event handlers for that object.

For example:

from win32com.client import DispatchWithEvents
from pyxll import xl_app, xl_macro

# Event handler class for Worksheet events
class WorksheetEventHandler:
    def OnSelectionChange(self, target):
         selection = self.Application.Selection
         print("Selection changed: " + selection.GetAddress())

# Global event handler. As long as this object isn't deleted it will
# keep handling events.
_sheet_with_events = None

# The following can be called from a macro or menu function
@xl_macro
def subscribe_to_events():
    # Use the global event handler
    global _sheet_with_events

    xl = xl_app()
    sheet = xl.ActiveSheet
    _sheet_with_events = DispatchWithEvents(sheet, WorksheetEventHandler)

In the above code we attach the WorksheetEventHandler to the active Excel Worksheet. As long as the _sheet_with_events object exists the event handler will be called whenever Excel raises the Worksheet.SelectionChange event.

If you wanted to subscribe to events when Excel starts rather than from a macro you can't simply do the same thing from the body of the macro as it's not safe to call into Excel then. Instead you must use pyxll.schedule_call to schedule a function that will be called when it is safe to call into Excel. If you use this in the body of your module the function will be called after Excel is started once the COM interface is ready, for example the above could be changed to:

from win32com.client import DispatchWithEvents
from pyxll import xl_app, schedule_call
_sheet_with_events = None

def subscribe_to_events():
    global _sheet_with_events

    xl = xl_app()
    sheet = xl.ActiveSheet
    _sheet_with_events = DispatchWithEvents(sheet, WorksheetEventHandler)

# Schedule a call to 'subscribe_to_events' to be called later when it's safe.
schedule_call(subscribe_to_events)

 

Our event handler above will be called for all Worksheet events, not just the SelectionChange event. This will result in a lot of errors because we have only implemented one of the possible event handlers.

Rather than implement each event handler method we can use a Python metaclass to supply the missing event handlers. Metaclasses are a fairly advances Python topic which we won't go to in detail here except to say that they are a way of changing a class's behaviour when the class is constructed. If you are interested you will find lots of information online or in the Python documentation.

The following code is a metaclass that adds any missing event handler methods by looking at the COM object the event handler class is constructed with. If any event handler methods are missing, it adds a new one using its null_event_handler method.

from functools import partial
import logging

_log = logging.getLogger(__name__)

class EventHandlerMetaClass(type):
    """
    A meta class for event handlers that don't repsond to all events.
    Without this an error would be raised by win32com when it tries
    to call an event handler method that isn't defined by the event
    handler instance.
    """
    @staticmethod
    def null_event_handler(event, *args, **kwargs):
        _log.debug(f"Unhandled event '{event}'")
        return None

    def __new__(mcs, name, bases, dict):
        # Construct the new class.
        cls = type.__new__(mcs, name, bases, dict)

        # Create dummy methods for any missing event handlers.
        cls._dispid_to_func_ = getattr(cls, "_dispid_to_func_", {})
        for dispid, name in cls._dispid_to_func_.items():
            func = getattr(cls, name, None)
            if func is None:
                null_handler = EventHandlerMetaClass.null_event_handler
                method = partial(null_handler, name)
                setattr(cls, name, method)

        return cls

Now we need to add this metaclass to our event handler class. This is done using the metaclass class keyword in Python 3, or the __metaclass__ class attribute in Python 2.

class WorksheetEventHandler(metaclass=EventHandlerMetaClass):
    def OnSelectionChange(self, target):
        selection = self.Application.Selection
        print("Selection changed: " + selection.GetAddress())

Or in Python 2,

class WorksheetEventHandler:
    __metaclass__ = EventHandlerMetaClass

    def OnSelectionChange(self, target):
        selection = self.Application.Selection
        print("Selection changed: " + selection.GetAddress())

 

Using the techniques from this article you will be able to write event handlers for all Excel events. For more information about what Excel events are available, see Microsoft's Excel Object Model documentation.

Related articles

Powered by Zendesk