PyXLL

Calling back into Excel from Python

While worksheet functions don't usually require to call back into Excel, it's usual that menu items and macros do.

Occasionally worksheet functions may also want to call back into Excel. For them to do so, they must be registered as macro sheet equivalent functions (see xl_func).

Macro sheet functions

The Excel C API provides a number of functions for querying information from Excel. A few of those are also available to call from PyXLL.

xlfCaller
xlfGetDocument
xlfGetWorkspace
xlfGetWorkbook
xlfGetWindow
xlfWindows

The functions above are all in the pyxll module. They have the same signature as their C counterparts and are simply wrappers of them.

Of these functions, the most likely to be found useful is xlfCaller. It returns the address of the calling cell when called from within a macro sheet equivalent worksheet function. There is an example of its use in the automation example provided with PyXLL.

Automation / COM

Excel has a well known COM API that makes programming macros and menu functions in Python as familiar as writing them in VBA.

PyXLL provides a function GetActiveObject that returns the Excel Window COM object for the current Excel instance.

The code below shows how to get the Excel Application instance using PyXLL and win32com

    import pyxll

    import win32com.client

    def xl_app()

        xl_window = pyxll.GetActiveObject()

        xl_app = win32com.client.Dispatch(xl_window).Application

        return xl_app

It's better to use pyxll.GetActiveObject than win32com.client.GetActiveObject("Excel.Application") as it will always give you the current instance of Excel, whereas getting it by name will return you any running instance which may not be the same if you're running more than one instance of Excel.

Using the Excel COM API to modify the current worksheet is something that you might do from a menu item or from a macro. In some cases you may also want to do it from a worksheet function. Some versions of Excel will block calls to the COM API while calling a worksheet function, and so if you try you will cause Excel to hang, even if using a macro sheet equivalent worksheet function.

Because of the deadlock problems associated with calling back into Excel from a worksheet function, PyXLL has a function async_call. It takes a callable object and calls it in a background thread. It returns immediately and so doesn't block waiting for the call back to Excel.

async_call(func, *args, **kwargs):

async_call is a function that schedules func to be called from a background thread. It returns immediately.
func is a callable object to be called in the background thread.
args and kwargs are passed to func when it's called.

When calling back into Excel and modifying the current sheet from a macro sheet equivalent function it's easy to end up with a circular dependency. This is because macro sheet equivalent functions with any var arguments or return value are considered volatile by Excel, so any change to the worksheet causes them to be re-evaulated.

To help with this, PyXLL keeps track of the arguments and return values for worksheet functions when it's likely that a circular dependency will occur and prevents that from happening. If you decide not to use async_call and to use another thread yourself you need to be careful of circular dependencies.