Handling Errors ¶

Exceptions raised by a UDF ¶

Whenever an unhandled exception is raised in a Python function, PyXLL will write it to the log file and return an error to Excel.

If no error handler is set, an Excel error code will be returned. The exact error code returned depends on the exception type as follows:

You can customize PyXLL’s error handling with the error-handler setting in the PYXLL section of the pyxll.cfg config file. You specify the function as a list of dotted references, much as you would for a Python import statement.

[PYXLL]
;
; Set custom error handler function
;
error_handler = your_module.error_handler_function

The error handler should be a function that takes three arguments: the exception type, the exception value and traceback of the uncaught exception, e.g.:

def error_handler_function(exc_type, exc_value, exc_traceback):
    """
    Convert a Python exception to a string value
    of form "<exception_type_name>: <value".
    """
    error = "##" + getattr(exc_type, "__name__", "Error")
    msg = str(exc_value)
    if msg:
        error += ": " + msg
    return error

When the error handler is executed, the return value of the error handler becomes the value of the cell from which it was referenced. See Error Handling for details.

Passing Errors as Values ¶

Sometimes it is useful to be able to pass a cell value from Excel to python (or vice-versa) when the cell value is actually an error.

1. Any function with return type var (or a type that derives from it) will return an error code to Excel if an Exception is returned. The exact error code depends on the type of the exception, following the table in the section above.

   This is useful when you want to return an array of data (or other array-like data, e.g. a pandas DataFrame) and where only some values should be returned as errors. By setting the values that should be errors to instances of exceptions they will appear in Excel as errors.

2. Alternatively, the special type: float_nan can be used.

   float_nan behaves in almost exactly the same way as the normal float type. It can be used as an array type, or as an element type in a numpy array, e.g. numpy_array<float_nan>. The only difference is that if the Excel value is an error or a non-numeric type (e.g. an empty cell), the value passed to python will be float(‘nan’) or #QNAN!, which is equivalent to numpy.nan.

   The two different float types exist because sometimes you don’t want your function to be called if there’s an error with the inputs, but sometimes you do. There is a slight performance penalty for using the float_nan type when compared to a plain float.

PyXLL maps Excel errors to Python Exception types as specified in the following table:

Retrieving Error Information ¶

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 by calling PyXLL’s get_last_error function.

get_last_error takes a cell reference and returns the last error for that cell as a tuple of (exception type, exception value, traceback). The cell reference may either be a XLCell or a COM Range object (the exact type of which depend on the com_package setting in the config.

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, because to do so would add additional performance overhead to every function call.

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 = 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)