Utility Functions

reload

reload()

Causes the PyXLL addin and any modules listed in the config file to be reloaded once the calling function has returned control back to Excel.

If the ‘deep_reload’ configuration option is turned on then any dependencies of the modules listed in the config file will also be reloaded.

The Python interpreter is not restarted.

rebind

rebind()

Causes the PyXLL addin to rebuild the bindings between the exposed Python functions and Excel once the calling function has returned control back to Excel.

This can be useful when importing modules or declaring new Python functions dynamically and you want newly imported or created Python functions to be exposed to Excel without reloading.

Example usage:

from pyxll import xl_macro, rebind

@xl_macro
def load_python_modules():
    import another_module_with_pyxll_functions
    rebind()

xl_app

xl_app(com_package=None)

Gets the Excel Application COM object and returns it as a win32com.Dispach, comtypes.POINTER(IUknown), pythoncom.PyIUnknown or xlwings.App depending on which COM package is being used.

Parameters:com_package (string) – The Python package to use when returning the COM object. It should be None, ‘win32com’, ‘comtypes’, ‘pythoncom’ or ‘xlwings’. If None the com package set in the configuration file will be used, or ‘win32com’ if nothing is set.
Returns:The Excel.Application COM object.

xl_version

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
  • 15.0 => Excel 2013
  • 16.0 => Excel 2016

async_call

async_call(callable, *args, **kwargs)

Calls a callable object (e.g. a function) in a background thread.

This can be useful when calling back into Excel (e.g. updating a cell value) from a worksheet function.

When using this function from a worksheet function care must be taken to ensure that an infinite loop doesn’t occur (e.g. if it writes to a cell that’s an input to the function, which would cause the function to be called again and again locking up Excel).

Note that and Excel COM objects created in the main thread should not be used in the background thread and doing so may cause Excel to crash.

Parameters:
  • callable – Callable object to call from a background thread.
  • args – Arguments to pass to the callable object.
  • kwargs – Keyword arguments to pass to the callable object.

Example usage:

from pyxll import xl_func, xl_app, xlfCaller, async_call

@xl_func(macro=True)
def set_values(rows, cols, value):
    """copies `value` to a range of rows x cols below the calling cell"""

    # get the address of the calling cell
    caller = xlfCaller()
    address = caller.address

    # the update is done asynchronously so as not to block Excel
    # by updating the worksheet from a worksheet function
    def update_func():
        xl = xl_app()
        xl_range = xl.Range(address)

        # get the cell below and expand it to rows x cols
        xl_range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))

        # and set the range's value
        xl_range.Value = value

    # kick off the asynchronous call the update function
    pyxll.async_call(update_func)

    return address

get_config

get_config()
Returns:the PyXLL config as a ConfigParser.SafeConfigParser instance

See also Configuration.

get_dialog_type

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

get_last_error

get_last_error(xl_cell)

When a Python function is called from an Excel worksheet, if an uncaught exception is raised PyXLL caches the exception and traceback as well as logging it to the log file.

The last exception raised while evaluating a cell can be retrieved using this function.

The cache used by PyXLL to store thrown exceptions is limited to a maximum size, and so if there are more cells with errors than the cache size the least recently thrown exceptions are discarded. The cache size may be set via the error_cache_size setting in the config.

When a cell returns a value and no exception is thrown any previous error is not discarded. This is because doing so would add additional performance overhead to every function call.

Parameters:xl_cell – An XLCell instance or a COM Range object (the exact type depends on the com_package setting in the config.
Returns:The last exception raised by a Python function evaluated in the cell, as a tuple (type, value, traceback).

Example usage:

from pyxll import xl_func, xl_menu, xl_version, get_last_error
import traceback


@xl_func("xl_cell: string")
def python_error(cell):
    """Call with a cell reference to get the last Python error"""
    exc_type, exc_value, exc_traceback = pyxll.get_last_error(cell)
    if exc_type is None:
        return "No error"

    return "".join(traceback.format_exception_only(exc_type, exc_value))


@xl_menu("Show last error")
def show_last_error():
    """Select a cell and then use this menu item to see the last error"""
    selection = xl_app().Selection
    exc_type, exc_value, exc_traceback = get_last_error(selection)

    if exc_type is None:
        xlcAlert("No error found for the selected cell")
        return

    msg = "".join(traceback.format_exception(exc_type, exc_value, exc_traceback))
    if xl_version() < 12:
        msg = msg[:254]

    xlcAlert(msg)

get_type_converter

get_type_converter(src_type, dest_type)

Returns a function to convert objects of type src_type to dest_type.

Even if there is no function registered that converts exactly from src_type to dest_type, as long as there is a way to convert from src_type to dest_type using one or more intermediate types this function will create a function to do that.

Parameters:
  • src_type (string) – name of type to convert from
  • dest_type (string) – name of type to convert to
Returns:

function to convert from src_type to dest_type

load_image

load_image(filename)

Loads an image file and returns it as a COM IPicture object suitable for use when customizing the ribbon.

This function can be set at the Ribbon image handler by setting the loadImage attribute on the customUI element in the ribbon XML file.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
            loadImage="pyxll.load_image">
    <ribbon>
        <tabs>
            <tab id="CustomTab" label="Custom Tab">
                <group id="Tools" label="Tools">
                    <button id="Reload"
                            size="large"
                            label="Reload PyXLL"
                            onAction="pyxll.reload"
                            image="reload.png"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Or it can be used when returning an image from a getImage callback.

Parameters:filename (string) – Filename of the image file to load. This may be an absolute path or relative to the ribbon XML file.
Returns:A COM IPicture object (the exact type depends on the com_package setting in the config.