New in PyXLL 5.12
The @xl_event decorators are used to register Python functions that will be called when specific
Excel Application events occur.
The events that can be handled are the same as the Excel Application events available through VBA, and the Python functions have the same arguments as their VBA counterparts.
Excel Application events can be handled using Python function decorated with one of the @xl_event decorators.
When an Excel Application event fires, all the registered event handler functions will be called. When there are multiple event handler functions registered for the same event the order in which the registered functions will be called is not guaranteed.
Event handler signatures mirror the VBA Application event signatures. Some events pass no arguments. Refer to Excel Application Events and the Excel VBA Object Browser for the precise argument list.
The following code registers a function to be called whenever the Excel Application AfterCalculate event is
fired:
from pyxll import xl_event
@xl_event("AfterCalculate")
def on_after_calculate():
pass
To simplify registering Excel event handlers, helpers are provided for each of the Excel Application events so that the event name can be omitted.
If you use auto-completion in your Python IDE then you will prefer to use these helpers over the event name. Elsewhere in these docs you will see the helpers used in preference to the named form, but both are equivalent.
Each shortcut decorator uses the underscore naming style instead of the VBA camel case style.
For example, the below is equivalent to the code above, but using the @xl_event.after_calculate helper in
place of @xl_event("AfterCalculate")
from pyxll import xl_event
@xl_event.after_calculate
def on_after_calculate():
print("Excel has been calculated")
All of the Excel Application events are available through the @xl_event decorator
and helper decorators.
For a complete list of available events please see the API documentation Excel Application Events.
The Excel Application events are the same as the Application events available through VBA, and are documented in the VBA Object Browser in Excel, as well as online in Microsoft’s documentation.
For event handler functions that take Excel objects as arguments, these arguments are passed as COM objects using the configured Python COM package. Supported Python COM packages are pywin32 (the default option) or comtypes.
The following example shows how to write a function that handles the Application.SheetActivate event. In VBA, this
event has a Sheet argument, and in Python the sheet is passed as a Sheet COM object using the default Python
COM package.
from pyxll import xl_event
@xl_event.sheet_activate
def on_sheet_activated(sheet):
"""This handles the Excel Workbook.SheetActivate event and is passed a COM Sheet object."""
# The sheet is an Excel Sheet object as has the same properties as the corresponding VBA Sheet object.
sheet_name = sheet.Name
# Log a message to show the event has been handled.
_log.info("EXAMPLE: Workbook.SheetActivate event for '%s' handled by %s" % (sheet_name, __file__))
To specify which Python COM package should be used, use the com_package kwargs to @xl_event.
The following example is the same as the above, but using the comtypes package.
from pyxll import xl_event
@xl_event.sheet_activate(com_package="comtypes")
def on_sheet_activated(sheet):
"""This handles the Excel Workbook.SheetActivate event and is passed a comtypes Sheet object."""
# The sheets is a ``comtypes`` COM object corresponding to the Excel Sheet object type
sheet_name = sheet.Name
# Log a message to show the event has been handled.
_log.info("EXAMPLE: Workbook.SheetActivate event for '%s' handled by %s" % (sheet_name, __file__))
The default com_package can be selected by setting com_package in the [PYXLL] section of the pyxll.cfg
config file.
Event handlers run in Excel’s main thread and may be called while Excel is busy. Keep handlers fast and avoid long running work.
It is safe to use xl_app and call into Excel inside an event handler.
If you need to do heavier processing, schedule it on a background thread. Remember however that you cannot
call back into Excel from a background thread and must use schedule_call if you need to call
into Excel.
Exceptions raised by event handlers are logged and do not prevent other handlers from running. Use logging in handlers to aid troubleshooting.
Event handlers are registered when their modules are loaded by PyXLL.
Handlers are un-registered and re-registered when the module they’re declared in is reloaded.
When registering a function as an event handler, by default, that function will be called whenever the Excel event is fired.
Sometimes you might only want your handler to be called for some events. For example, if you have a
SheetSelectionChange event you might want to only do something if the sheet belongs to a specific workbook.
All @xl_event decorators take a filter kwarg. This can be set to another function that takes the
same arguments as the event handler. It will be called before the event handler, and the event handler will
only be called if the filter function returns True.
Filters run on every event fire and will block Excel. Keep them lightweight to avoid performance problems.
For example, the following filter function returns True if the worksheet belongs to a workbook with a
specific name:
from pyxll import xl_event
# The filter function has the same signature as the event handler
def sheet_in_correct_workbook(sheet):
# sheet is an Excel Worksheet COM object
book = sheet.Parent
# return True if the parent workbook name is what we're looking for
return book.Name == "MyBookName.xlsx"
@xl_event.sheet_activate(filter=sheet_in_correct_workbook)
def on_sheet_activate(sheet):
# This only gets called if the filter function returns True
print(f"Sheet activated: {sheet.Name}")
Using a filter function as above avoids cluttering your event handler with conditional code that you might like to reuse between event handlers.
A common pattern is to use a more generic factory function to return a filter function. We could re-write the above example with a filter function factory so that it can be reused for different workbook names as follows:
from pyxll import xl_event
def sheet_in_workbook(workbook_name):
"""Return a filter function that takes a Sheet object."""
# This is the function our outer function will return and is
# what will be used as our filter function.
def sheet_in_correct_workbook(sheet):
# sheet is an Excel Worksheet COM object
book = sheet.Parent
# return True if the parent workbook name matched our chosen 'workbook_name'
return book.Name == workbook_name
# Return the filter function that is using our workbook_name
return sheet_in_correct_workbook
# Now we can specify the workbook name when creating the filter function
@xl_event.sheet_activate(filter=sheet_in_workbook("MyBookName.xlsx"))
def on_sheet_activate(sheet):
# This only gets called if the filter function returns True
print(f"Sheet activated: {sheet.Name}")
