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.
PyXLL provides two standard error handlers to choose from.
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:
Error Source | pyxll.error_handler | pyxll.quiet_error_handler | No Handler |
---|---|---|---|
Worksheet Function | Return error as string | Return error as string | Nothing (returns #NA! etc.) |
Macro | Return error as string | Return error as string | Nothing (returns #NA! etc.) |
Menu Item | Show error in message box | Do nothing | Do nothing |
Ribbon Action | Show error in message box | Do nothing | Do nothing |
Module Import | Show error in message box | Do nothing | Do nothing |
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.
Excel error | Python Exception type |
---|---|
#NULL! | LookupError |
#DIV/0! | ZeroDivisionError |
#VALUE! | ValueError |
#REF! | ReferenceError |
#NAME! | NameError |
#NUM! | ArithmeticError |
#NA! | RuntimeError |
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. |