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 and command 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.

In order to call most of these functions from an Excel worksheet function the function must be registered as a macro sheet equivalent function (see Exposing functions as UDFs).

pyxll.xlfCaller()

Returns:calling cell as an XLCell instance.

Callbable from any function, but most properties of XLCell are only accessible from macro sheet equivalent functions

pyxll.xlfGetDocument(arg_num[, name])

Parameters:

• arg_num (int) – number from 1 to 88 specifying the type of document information to return
• name (string) – sheet or workbook name

Returns:

depends on arg_num

pyxll.xlfGetWorkspace(arg_num)

Parameters:arg_num (int) – number of 1 to 72 specifying the type of workspace information to return Returns:depends on arg_num

pyxll.xlfGetWorkbook(arg_num[, workbook])

Parameters:

• arg_num (int) – number from 1 to 38 specifying the type of workbook information to return
• workbook (string) – workbook name

Returns:

depends on arg_num

pyxll.xlfGetWindow(arg_num[, window])

Parameters:

• arg_num (int) – number from 1 to 39 specifying the type of window information to return
• window (string) – window name

Returns:

depends on arg_num

pyxll.xlfWindows([match_type] [,mask])

Parameters:

• match_type (int) –

  a number from 1 to 3 specifying the type of windows to match

  1 (or omitted) = non-add-in windows

  2 = add-in windows

  3 = all windows

• mask (string) – window name mask

Returns:

list of matching window names

pyxll.xlcAlert(alert)

Pops up an alert window.

Callbable from a macro or menu function only

Parameters:alert (string) – text to display

pyxll.xlcCalculation(calc_type)

set the calculation type to automatic or manual.

Callbable from a macro or menu function only

Parameters:calc_type (int) –

xlCalculationAutomatic

or xlCalculationSemiAutomatic

or xlCalculationManual

pyxll.xlcCalculateNow()

recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are volatile functions.

Equivalent to pressing F9.

Callbable from a macro or menu function only

pyxll.xlcCalculateDocument()

recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are volatile functions for the current worksheet only

Callbable from a macro or menu function only

pyxll.xlAsyncReturn(handle, value)

used by asynchronous functions to return the result to Excel see Asynchronous functions

This function can be called from any thread and doesn’t have to be from a macro sheet equivalent function

Parameters:

• handle (object) – async handle passed to the worksheet function
• value (object) – value to return to Excel

pyxll.xlAbort(retain=True)

Yields the processor to other tasks in the system and checks whether the user has pressed ESC to cancel a macro or workbook recalculation.

Parameters:retain (bool) – If False and a break condition has been set it is reset, otherwise don’t change the break condition. Returns:True if the user has pressed ESC, False otherwise.

xlCalculationAutomatic = 1

xlCalculationSemiAutomatic = 2

xlCalculationManual = 3

The functions above have the same signature as their C counterparts and are simply wrappers of them. For more detailed information on their behavior refer to the Excel C API documentation.

Some additional functions are provided that return information about the current state of Excel but are not simply wrappers of the Excel C API functions.

pyxll.get_active_object()

pythoncom must be installed for this function to work

Returns:the PyIUnknown Excel COM object associated with the current window

pyxll.get_dialog_type()

Returns:the type of the current dialog that initiated the call into the current Python function

xlDialogTypeNone

or xlDialogTypeFunctionWizard

or xlDialogTypeSearchAndReplace

xlDialogTypeNone = 0

xlDialogTypeFunctionWizard = 1

xlDialogTypeSearchAndReplace = 2

Other non-macro functions

The following functions also return information about Excel and the current state of PyXLL, but are not macro sheet functions and may be called from anywhere.

pyxll.get_config()

Returns:the PyXLL config as a ConfigParser.SafeConfigParser instance

See also Config file.

pyxll.xl_version()

Returns:the version of Excel the addin is running in, as a float

8.0 => Excel 97

9.0 => Excel 2000

10.0 => Excel 2002

11.0 => Excel 2003

12.0 => Excel 2007

14.0 => Excel 2010

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 get_active_object that returns the Excel Window COM object for the current Excel instance.

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

from pyxll import get_active_object
import win32com.client

def xl_app():
    xl_window = get_active_object()
    xl_app = win32com.client.Dispatch(xl_window).Application
    return xl_app

It’s better to use get_active_object 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 [1] . 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.

pyxll.async_call(func [, *args] [, **kwargs])

async_call schedules func to be called from a background thread.

If args or kwargs contain any COM objects they will be marshalled across the thread boundary automatically.

Returns immediately.

Parameters:

• func – callable object (e.g. a function) to be called in the background thread
• args (tuple) – passed to func when it’s called
• kwargs (dict) – 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 possible to end up with a circular dependency. Macro sheet equivalent functions with any xl_cell arguments 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.

Footnotes

[1]	Not to be confused with asynchronous functions that return their result to Excel asynchronously. async_call is simply a function that queues a callable object to be called later. See also Asynchronous functions.