What’s new in PyXLL 3
PyXLL 3 introduces a number of new features that make it even easier to make rich, interactive Excel
spreadsheets in Python.
Internally a lot has changed to bring you these new features and ensure that PyXLL retains the high
quality and reliability that you’re used to from previous versions.
When returning an array PyXLL can automatically resize the range of the array formula in Excel so it
expands or contracts to match the size of the array being returned.
Array functions can be made to automatically resize by setting the keyword argument auto_resize=True
xl_func, or it can be enabled for all array functions in the config file by setting
auto_resize_arrays = 1
Macros and menus written using
xl_menu can now be assigned
keyboard shortcuts using the shortcut keyword argument, or set in the config.
Ever wanted to write an addin that uses the Excel ribbon interface? Previously the only way to do
this was to write a COM addin, which requires a lot of knowledge, skill and perseverance! Now
you can do it with PyXLL by defining your ribbon as an XML document and adding it to your PyXLL
config. All the callbacks between Excel and your Python code are handled for you.
See Customizing the Ribbon for more detailed information or try the example included in the download.
PyXLL can stream live data into your spreadsheet without you having to write any extra services
or register any COM controls. Any Python function exposed to Excel through PyXLL can return a
RTD type that acts as a ticking data source; Excel updates whenever the returned
RTD publishes new data.
See Real Time Data for more detailed information or try the example included in the download.
xl_macro need to know the argument and return types to be
able to tell Excel how they should be called. In previous versions that was always done by
passing a ‘signature’ string to these decorators.
Now in PyXLL 3 the signature is entirely optional. If a signature is not supplied PyXLL will inspect
the function and determine the signature for you.
If you use Python type annotations when declaring the function, PyXLL will use those when determining
the function signature. Otherwise all arguments and the return type will be assumed to be var.
Python functions with default keyword arguments now preserve their default value when called from
Excel with missing arguments. This means that a function like the one below
when called from Excel with b or c missing will be invoked with the correct default values for b and c.
def func_with_kwargs(a, b=1, c=2):
return a + b + c
Many problems with installing PyXLL come down to the fact that Python isn’t installed as the default
Python installation on the PC. This means that PyXLL can’t find the Python dll or the Python packages
as their locations are not set globally.
This affects any distribution that doesn’t set itself up as the default Python install (most
notably Anaconda, PortablePython and any virtualenv).
There were ways to get around this and make it work, but it always should have been easier. Now it is!
To set which Python to use with PyXLL now all you have to do is set it up in your pyxll.cfg file
executable = <your chosen python.exe here>
When this is set PyXLL will try to find the right Python DLL to load and try to locate where the
Python libraries are installed. If it still can’t find either of those you can tell it by setting
dll and pythonhome in the same section of the config.
If you’ve used PyXLL for a while you will have noticed that when you reload PyXLL only the modules
listed in your pyxll.cfg file get reloaded. If you are working on a project that has multiple modules
and not all of them are added to the config those won’t get reloaded, even if modules that are listed
in the config file import them.
PyXLL can now track all the imports made by each module listed in the config file, and when you
reload PyXLL all of those modules will be reloaded in the right order.
This feature is enabled in the config file by setting
Sometimes it’s not convenient to have to pick through the log file to determine why a particular
cell is failing to calculate.
The new function
get_last_error takes an
XLCell or a COM Range and
returns the last exception (and traceback) to have occurred in that cell.
This can be used in menu functions or other worksheet functions to give end users better feedback
about any errors in the worksheet.
PyXLL can now be reloaded or it can rebind its Excel functions using the new Python functions
PyXLL integrates with the Excel-DNA IntelliSense Excel Addin to provide function completion and argument help
sa you enter formulas in Excel.
By adding the Excel-DNA IntelliSense addin to Excel (as well as PyXLL) your custom Python functions will
automatically be recognized and it will give you IntelliSense for your functions.
The Excel-DNA IntelliSense addin can be downloaded from https://github.com/Excel-DNA/IntelliSense/releases.
Note: The Excel-DNA IntelliSense addin is currently in beta.