Error Handling ¶

Introduction ¶

Any time a PyXLL function raises an uncaught Exception, it will be written to the log file as an error.

If you need to figure out what is going wrong, the log file should be your first piece of evidence. The location of the log file is set in the PyXLL config file, and by default it is in the logs folder alongside the PyXLL add-in.

In addition to the log file, PyXLL provides ways of handling errors to present them to the user directly when they occur. The full exception and stack trace are always written to the log file, but in many cases providing the user with some details of the error is sufficient to let them understand the problem without having to resort to the log file.

For example, if a worksheet function fails Excel’s default behaviour is to show an error like #NA. Consider the following function:

@xl_func
def my_udf(x, y):
    if not 1 <= x <= 100:
         raise ValueError("Expected x to be between 1 and 100")
    return do_something(x, y)

If you call this from Excel with x outside of 1 and 100, without an error handler the user will see #VALUE!. They can look in the log file to see the full error, but an error handler can be used to return something more helpful. Using the standard error handler pyxll.error_handler ##ValueError: Expected x to be between 1 and 100 would be returned [1].

The configured error handler will be called for all types of functions when an uncaught Exception is raised, not simply worksheet functions.

Standard Error Handlers ¶

PyXLL provides two standard error handlers to choose from.

• pyxll.error_handler
• pyxll.quiet_error_handler

These are configured by setting error_handler in the configuration file, e.g.:

[PYXLL]
error_handler = pyxll.error_handler

The following table shows how the two different error handlers behave for the different sources of errors:

Custom Error Handlers ¶

For cases where the provided error handling isn’t suitable, you can provide your own error handler.

An error handler is simply a Python function that you reference from your configuration file, including the module name, for example:

[PYXLL]
error_handler = my_error_handler.error_handler

The error handler takes four [2] arguments, context (ErrorContext), exc_type, exc_value and exc_traceback. context is a ErrorContext object that contains additional information about the error that has occurred, such as the type of function that was being called.

The following shows a custom error handler that returns a string if the function type was a worksheet function (UDF) or macro. For all other types, it calls pyxll.error_handler, delegating error handling to PyXLL’s standard handler.

from pyxll import error_handler as standard_error_handler

def error_handler(context, exc_type, exc_value, exc_traceback):
    """Custom PyXLL error handler"""

    # For UDFs return a preview of the error as a single line
    if context.error_type in (ErrorContext.Type.UDF, ErrorContext.Type.MACRO):
        error = "##" + getattr(exc_type, "__name__", "Error")
        msg = str(exc_value)
        if msg:
            error += ": " + msg
        return error

    # For all other error types call the standard error handler
    return standard_error_handler(context, exc_type, exc_value, exc_traceback)

PyXLL will still log the exception, so there is no need to do that in your handler.

If you want your error handler to return an error code to Excel instead of a string, return the Exception value. Python Exceptions are converted to Excel errors as per the following table.

Retrieving Error Information ¶

When a Python function is called an Excel as a worksheet function, 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)

Footnotes

[1]	Sometimes it’s useful to actually return an error code (eg #VALUE!) to Excel. For example, if using the =ISERROR Excel function. In those cases, you should not set an error handler, or use a custom error handler that returns a Python Exception.

[2]

Prior to PyXLL 4.3, error handlers only took three arguments and didn’t have the context argument. PyXLL is backwards compatible with older versions. If you have an old error handler that only takes three arguments, this will be handled automatically and that error handler will only be called for worksheet functions (UDFs) and macros.