PyXLL User Guide
Release 4.2.4
PyXLL Ltd.
Sep 10, 2019
Contents
1 Introduction to PyXLL 1
1.1 What is PyXLL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 How does it work? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Before You Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.4 Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2 What’s new in PyXLL 4 5
2.1 New Features and Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.2 Important notes for upgrading from previous versions . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 User Guide 13
3.1 Installing the PyXLL Excel Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3.2 Configuring PyXLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
3.3 Worksheet Functions (UDFs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3.4 Menu Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3.5 Customizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.6 Context Menu Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.7 Macro Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
3.8 Real Time Data (RTD) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.9 Asynchronous Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
3.10 Python as a VBA Replacement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
4 API Reference 70
4.1 Function Decorators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
4.2 Utility Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
4.3 Ribbon Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
4.4 Event Handlers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
4.5 Excel C API Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
4.6 Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
5 Examples 91
5.1 UDF Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
5.2 Pandas Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
5.3 Cached Objects Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
5.4 Custom Type Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
5.5 Menu Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
5.6 Macros and Excel Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
i
CHAPTER 1
Introduction to PyXLL
• What is PyXLL?
• How does it work?
• Before You Start
• Next Steps
– Calling a Python Function in Excel
– Additional Resources
1.1 What is PyXLL?
PyXLL v4 is an Excel Add-In that enables developers to extend Excel’s capabilities with Python code.
PyXLL makes Python a productive, flexible back-end for Excel worksheets, and lets you use the familiar Excel user
interface to interact with other parts of your information infrastructure.
With PyXLL, your Python code runs in Excel using any common Python distribution(e.g. Anaconda, Enthought’s
Canopy or any other CPython distribution from 2.3 to 3.7).
Because PyXLL runs your own full Python distribution you have access to all third party Python packages such as
NumPy, Pandas and SciPy and can call them from Excel.
Example use cases include:
• Calling existing Python code to perform calculations in Excel
• Data processing and analysis that’s too slow or cumbersome to do in VBA
• Pulling in data from external systems such as databases
• Querying large datasets to present summary level data in Excel
1
PyXLL User Guide, Release 4.2.4
• Exposing internal or third party libraries to Excel users
1.2 How does it work?
PyXLL runs Python code in Excel according to the specifications in its config file, in which you configure how Python
is run and which modules PyXLL should load. When PyXLL starts up it loads those modules and exposes certain
functions that have been tagged with PyXLL decorators.
For example, an Excel user defined function (UDF) to compute the n
th
Fibonacci number can be written in Python as
follows:
from pyxll import xl_func
@xl_func
def fib(n):
"Naiive Fibonacci implementation."
if n == 0:
return 0
elif n == 1:
return 1
return fib(n-1) + fib(n-2)
The xl_func-decorated function fib is detected by PyXLL and exposed to Excel as a user-defined function.
Excel types are automatically converted to Python types based on an optional function signature. Where there is no
simple conversion (e.g. when returning an arbitrary class instance from a method) PyXLL stores the Python object
reference as a cell value in Excel. When another function is called with a reference to that cell PyXLL retrieves the
object and passes it to the method. PyXLL keeps track of cells referencing objects so that once an object is no longer
referenced by Excel it can be dereferenced in Python.
1.3 Before You Start
Existing users might want to study What’s new in PyXLL 4. Those upgrading from earlier versions will find the
Important notes for upgrading from previous versions useful. If you prefer to learn by watching, take a look at our
video guides and tutorials.
To begin with download PyXLL, ensuring that you select the right version for your versions of Excel and Python.
Note that you cannot mix 32 bit and 64 bit versions of Excel, Python and PyXLL – they all must be the same.
Install the add-in as per the installation instructions, making sure to update the configuration file.
Once PyXLL is installed you will be able to try out the examples workbook that is included in the download. All the
code used in the examples workbook is also included in the download.
Note that any errors will be written to the log file, so if you are having difficulties always look in the log file to see
what’s going wrong, and if in doubt please contact us.
1.4 Next Steps
1.4.1 Calling a Python Function in Excel
One of the main features of PyXLL is being able to call a Python function from a formula in an Excel workbook.
1.2. How does it work? 2
PyXLL User Guide, Release 4.2.4
First start by creating a new Python module and writing a simple Python function. To expose that function to Excel all
you have to do is to apply the xl_func decorator to it.:
from pyxll import xl_func
@xl_func
def hello(name):
return "Hello, %s" % name
Save your module and edit the pyxll.cfg file again to add your new module to the list of modules to load and add the
directory containing your module to the pythonpath.
[PYXLL]
modules = <add the name of your new module here>
[PYTHON]
pythonpath = <add the folder containing your Python module here>
Go to the Addins menu in Excel and select PyXLL -> Reload. This causes PyXLL to reload the config and Python
modules, allowing new and updated modules to be discovered.
Now in a worksheet you will find you can type a formula using your new Python function.:
=hello("me")
Using PyCharm, Eclipse or Visual Studio?
You can interactively debug Python code running in PyXLL with Eclipse, PyCharm, Visual Studio and other IDEs
by attaching them as a debugger to a running PyXLL. See our blog post Debugging Your Python Excel Add-In for
details.
If you make any mistakes in your code or your function returns an error you can check the log file to find out what the
error was, make and necessary changes to your code and reload PyXLL again.
1.4. Next Steps 3
PyXLL User Guide, Release 4.2.4
1.4.2 Additional Resources
The documentation explains how to use all the features of PyXLL, and contains a complete API reference. PyXLL’s
features are also well demonstrated in the examples included in download. These are a good place to start to learn
more about what PyXLL can do.
More example code can be found on PyXLL’s GitHub page.
If there is anything specifically you’re trying to achieve and can’t find an example or help in the documentation please
contact us and we will do our best to help.
1.4. Next Steps 4
CHAPTER 2
What’s new in PyXLL 4
Looking for an earlier version?
See 3.x/whatsnew for a detailed overview of the features added in PyXLL 3.
• New Features and Improvements
– Python 3.7 Support Added
– Pandas DataFrame and Series support
– Object Cache for returning complex types
– One dimensional arrays
– Pass dictionaries between Python and Excel
– RTD array formulas
– Functions with variable number of arguments
– Typed RTD and async values
– Customizable error handling
– Parameterised custom types
– Improved NumPy performance
– asyncio support for UDFs and RTD functions
• Important notes for upgrading from previous versions
– The signature for array types has changed: replace [] with [][]
– Exceptions thrown in UDFs are returned as human readable strings
5
PyXLL User Guide, Release 4.2.4
– Objects returned from UDFs are now returned as cached object handles
– No need to use objectcache example: replace ‘cached_object’ with ‘object’
– No need to use pyxll_utils.pandastypes
2.1 New Features and Improvements
2.1.1 Python 3.7 Support Added
PyXLL 4 is available for 32 and 64 bit versions of Python 3.7.
2.1.2 Pandas DataFrame and Series support
The Pandas types DataFrame and Series can be used with PyXLL without the need for 3rd party packages like
pyxll_utils.
When specifying a UDF function signature (see Worksheet Functions (UDFs)) the types dataframe and series
may now be used to indicate that a range of data will be accepted by the function, and PyXLL will automatically
construct a pandas DataFrame or Series to pass to the function (or if used as the return type, the pandas type will
automatically be converted to a range of data before being returned to Excel).
Both the dataframe and series types can be parameterized to control exactly how the Excel data will be converted
to and from the pandas types.
• dataframe, when used as an argument type
dataframe<index=0, columns=1, dtype=None, dtypes=None, index_dtype=None>
index Number of columns to use as the DataFrame’s index. Specifying more than one will result in a
DataFrame where the index is a MultiIndex.
columns Number of rows to use as the DataFrame’s columns. Specifying more than one will result in a
DataFrame where the columns is a MultiIndex. If used in conjunction with index then any column headers
on the index columns will be used to name the index.
dtype Datatype for the values in the dataframe. May not be set with dtypes.
dtypes Dictionary of column name -> datatype for the values in the dataframe. May not be set with dtype.
index_dtype Datatype for the values in the dataframe’s index.
• dataframe, when used as a return type
dataframe<index=None, columns=True>
index If True include the index when returning to Excel, if False don’t. If None, only include if the index is
named.
columns If True include the column headers, if False don’t.
• series, when used as an argument type
series<index=1, transpose=None, dtype=None, index_dtype=None>
index Number of columns (or rows, depending on the orientation of the Series) to use as the Series index.
transpose Set to True if the Series is arranged horizontally or False if vertically. By default the orientation
will be guessed from the structure of the data.
2.1. New Features and Improvements 6
PyXLL User Guide, Release 4.2.4
dtype Datatype for the values in the Series.
index_dtype Datatype for the values in the Series’ index.
• series, when used as a return type
series<index=True, transpose=False>
index If True include the index when returning to Excel, if False don’t.
transpose Set to True if the Series should be arranged horizontally, or False if vertically.
The Pandas types work with cached objects also, meaning you can return a pandas type as with the return type ‘object’
and an object handle will be returned to Excel, and pass that to a function with an argument type ‘dataframe’ or ‘series’
and the cached object will be passed to your function without having to reconstruct it.
2.1.3 Object Cache for returning complex types
Often it’s necessary to pass a Python object between functions. You might have one function that creates a complex
Python object that is used as an input to various other functions, and it’s not convenient or efficient to keep recreating
that object from lots of primitive inputs every time you need it.
PyXLL now allows you to pass objects around as easily as primitive values via its managed object cache. When a
function returns a Python type that can’t be converted to a simple Excel type, Python assigns it a unique handle and
returns that to Excel. Any function that takes an object can be passed that handle and PyXLL will fetch the object
from the cache and pass it to the Python method.
PyXLL takes care of making sure that once objects are no longer referenced they are removed from the cache so they
can be destroyed.
Cached objects are not persisted when Excel closes, and they are not saved with the workbook. This means that
every time you open a workbook that depends on cached objects it has to be recalculated so that the object cache is
repopulated.
To use the object cache use the object type in your function signature. If you are using the var type then returning
an object with no other type conversion will result in a cached object handle being returned.
RTD functions can return cached objects either implicitly with the return type rtd, or explicitly by using
rtd<object>.
This replaces the objectcache example and pyxll_utils.objectcache.
2.1.4 One dimensional arrays
In previous versions of PyXLL, array types were always expected to be 2d lists of lists, eg:
x = [[1, 2, 3], [4, 5, 6]]
This was a source of confusion when passing a single row or column of data from Excel to Python as the (incorrectly)
expected behaviour was that a simple 1d list of data would be passed to the function.
In PyXLL 4, it is now possible to specify in the function signature whether a function expects a 1d list of data or a 2d
list of lists.
For a 1d list, use the [] type suffix, e.g:
@xl_func("float[] numbers: float")
def py_sum(numbers):
total = 0
for x in numbers: # loop used to illustrate, could use 'sum' here
2.1. New Features and Improvements 7
PyXLL User Guide, Release 4.2.4
total += x
return total
For a 2d list of lists, use the [][] type suffix, e.g.:
@xl_func("float[][] numbers: float")
def py_sum(numbers):
total = 0
for row in numbers:
for x in row:
total += x
return total
Note This change is a breaking change and if you are upgrading from a previous version of PyXLL you may want to
disable this feature until you have updated your code. You can disable it by setting the following in your pyxll.cfg file:
[PYXLL]
always_use_2d_arrays = 1
Tip When returning 1d arrays, use the transpose option to xl_func if you want to return the array as a row instead
of a column (new in PyXLL 4.2).
2.1.5 Pass dictionaries between Python and Excel
A new dict type has been added for passing dictionaries between Python and Excel.
The following is a simple function that accepts an dictionary of integers keyed by strings. Note that the key and value
types are optional and default to var if not specified.
@xl_func("dict<str, int>: str")
def dict_test(x):
return str(x)
See Dictionary Types for more details.
2.1.6 RTD array formulas
RTD functions may now return arrays of data. This will simplify many cases where a table of ticking data is returned.
However, RTD array functions cannot be resized automatically.
Note: RTD functions may also now return cached objects. A useful pattern is to have an RTD function return a cached
object containing the latest data, and another function to explode that cached object into an array of data.
2.1. New Features and Improvements 8
PyXLL User Guide, Release 4.2.4
2.1.7 Functions with variable number of arguments
In PyXLL 4 it is now possible to expose functions taking a variable number of arguments to Excel. Functions taking
*
args are automatically exposed to Excel as variable argument functions. It is only possible to use a single type for
all of the
*
args however, so if you need them to be multiple different types you should use the var type.
For example, the following function takes a separator and a variable number of string arguments and joins them:
@xl_func("str sep, str
*
args: str")
def py_join(sep,
*
args):
return sep.join(args)
2.1.8 Typed RTD and async values
Both types rtd and async_handle can now be parameterized with the return type. This is used when converting
the returned value to Excel, so the conversion is no longer required to be done prior to returning the value.
e.g.
@xl_func("str x: rtd<object>")
def rtd_function(x):
# Calling 'MyRtd.value = x' updates Excel with a new cached object handle
return MyRtd(x)
@xl_func("str x: rtd<dataframe<index=True>>")
def rtd_function(x):
# Calling 'MyDataFrameRtd.value = df' updates Excel with an array
return MyDataFrameRtd(x)
2.1.9 Customizable error handling
When an unhandled Exception is raised during a worksheet function (UDF), previously PyXLL would log the excep-
tion and return an error to Excel.
This behaviour can now be customized, allowing more human readable errors to be returned to Excel.
A default error handler is provided that converts the unhandled Exception to a string and returns that.
Note that the error handler can return an object type that can be converted to an Excel type, and so returning a Python
Exception object will result in an Excel error being shown. Sometimes this is required so that functions like ISERROR
can be used.
To specify an error handler set the error_handler option in the config to a function, including the module name:
[PYXLL]
error_handler = pyxll.error_to_string
The error handler takes three arguments, exc_type, exc_value and exc_traceback. PyXLL will still log the exception,
so there is no need to do that in your handler. The default handler is as follows:
def error_to_string(exc_type, exc_value, exc_traceback):
"""Standard PyXLL error handler that converts an Exception to a string"""
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
2.1. New Features and Improvements 9
PyXLL User Guide, Release 4.2.4
2.1.10 Parameterised custom types
As seen in some of the new features above, types in PyXLL can now be parameterized (e.g. dataframe<index=1>
or rtd<object>).
Your own custom types registered with xl_arg_type and xl_return_type can also be parameterized in exactly
the same way.
Any function decorated with xl_arg_type and xl_return_type with named keyword arguments can be used
as a parameterized type. For example,
_lookups = {"MyCustomType:A": A, "MyCustomType:B", B} # etc...
_ci_lookups = {k.lower(): v for k, v in _lookups.items()} # lowercase lookup
# convert a named object to a Python value
@xl_arg_type("my_custom_type", "str")
def my_custom_type_from_var(x, case_sensitive=False):
if case_insensitive:
return _ci_lookups[x.lower()]
return _lookups[x]
# this Excel function takes a string, which is converted via 'my_custom_type_from_var'
@xl_func("my_custom_type<case_insensitive=True> x: var")
def do_something(x):
pass
The base type (str in the example above) can also be a function of the type parameters. Rather than using a string as
the base type, a function taking the same keyword arguments can be used instead. That function returns the base type
to be used. This can be used for even more generic types, e.g.:
def _get_base_type(dtype=float):
if dtype == float:
return "float[][]"
elif dtype == int:
return "int[][]"
raise NotImplementedError()
@xl_arg_type("my_array_type", _get_base_type)
def my_custom_type_from_var(x, dtype=float):
pass # convert array into instance of 'my_array_type'
# this Excel function takes an array, which is converted via 'my_custom_type_from_var'
@xl_func("my_array_type<dtype=int> x: var")
def do_something(x):
pass
As well as specifying the type parameters in the function signature, they can also be specified using xl_arg and
xl_return, or as kwargs to get_type_converter.
2.1.11 Improved NumPy performance
NumPy floating point array arguments and return types are now significantly faster.
Tests show as much as a 15x speed up for passing floating point numpy arrays (e.g. numpy_array<float>)
between Python and Excel.
2.1. New Features and Improvements 10
PyXLL User Guide, Release 4.2.4
2.1.12 asyncio support for UDFs and RTD functions
New in PyXLL 4.2 - requires Python 3.5.1 or higher
The Python keyword async introduced in Python 3.5 can now be used to declare an Excel function decorated with
xl_func as being asynchronous.
Asynchronous functions declared in this way are run in an asyncio EventLoop, managed by PyXLL.
The RTD methods RTD.connect and RTD.disconnect may also now be asynchronous using the async keyword.
They too will be run on the asyncio EventLoop managed by PyXLL.
This simplifies writing asynchronous code in Excel and reduces the need for user managed background threads and
thread pools.
The asyncio EventLoop used by PyXLL can be obtained using the new get_event_loop function. The default
behaviour is that this event loop runs on a single background thread, but this may be configured with an alternative
implementation specified in the pyxll.cfg config file (see Configuring PyXLL).
2.2 Important notes for upgrading from previous versions
PyXLL 4.0 contains some changes that may require you to make changes to your code and/or config before upgrading
from previous versions.
2.2.1 The signature for array types has changed: replace [] with [][]
This can be disabled for backwards compatibility
In previous versions, [] was used to mean a 2d array or range of values that was passed to Python as a list of lists. As
of PyXLL 4.0, arrays may now be either 1d or 2d, and [] is used to mean a 1d array. To pass 2d arrays you need to
change your signature to use [][] when your function accepts or returns a list of lists (e.g. replace float[] with
float[][]).
To disable this new feature and preserve the old behaviour where [] is used to indicate a 2d array or list of lists, set
the following in your pyxll.cfg
[PYXLL]
always_use_2d_arrays = 1
2.2.2 Exceptions thrown in UDFs are returned as human readable strings
This can be disabled for backwards compatibility
A new config option allows an error handler to convert Exceptions raised when calling a worksheet function to a more
human readable error message. If you are relying on Python functions to return Excel errors so that functions like
ISERROR works, then remove or comment out this option in your config file.
[PYXLL]
error_handler = pyxll.error_to_string
2.2. Important notes for upgrading from previous versions 11
PyXLL User Guide, Release 4.2.4
2.2.3 Objects returned from UDFs are now returned as cached object handles
If you have functions with the return type unspecified or set to var that return non-trivial Python objects, then those
objects will now be stored in the new object cache and a handle to that object will be displayed in Excel.
Previously objects of types not known to PyXLL were string converted using str and that string was returned to
Excel. If you require this behaviour, change the return type of the function to str, or string convert the object before
returning it from the function.
2.2.4 No need to use objectcache example: replace ‘cached_object’ with ‘object’
This is not required, but recommended
If you were previously using the Object Cache example (or the object cache module from the pyxll_utils package) then
you should now switch to using the built-in object cache.
The type to use in your function signatures that return or take an object as an argument is object. Functions accepting
or returning var may also accept or return cached objects.
2.2.5 No need to use pyxll_utils.pandastypes
This is not required, but recommended
If you were previously using pyxll_utils.pandastypes or the pandastypes.py example code then you can now use the
more powerful built-in types instead.
The datatype names are the same, so all that is required is to remove pandastypes from your pyxll.cfg file.
If you are using a customized version of the pandastypes code, it is recommended to rename your custom types so
they do not conflict with the built in ones.
2.2. Important notes for upgrading from previous versions 12
CHAPTER 3
User Guide
3.1 Installing the PyXLL Excel Add-In
You can find a video tutorial showing how to install PyXLL in our video guides and tutorials.
PyXLL works with any Python distribution, including Anaconda. For specific instructions about installing with Ana-
conda or Miniconda see anaconda.
Warning:
Excel comes in 64-bit and 32-bit versions, as do Python and PyXLL. The three products must be all 32-bit or
all 64-bit.
This web page shows you how to determine which version of Excel you are using.
1. Download the standalone PyXLL zip file from the download page. Select and download the correct version
depending on the versions of Python and Excel you want to use and agree to the terms and conditions.
2. Unpack the zipfile. PyXLL can be used with any Python distribution. Depending on how you have Python
installed on your system you may need to configure PyXLL so it knows where to find your Python installation.
PyXLL is packaged as a zip file. Simply unpack the zip file where you want PyXLL to be installed. There is no
installer to run - installation is completed by adding the PyXLL add-in to Excel after any necessary configuration
changes.
3. Edit the config file Once you’ve unzipped the PyXLL download the next thing to do is to edit the pyxll.cfg file.
Any text editor will do for this. The default configuration may be fine for you while you’re getting started, and
you can come back to it later if you want to make any changes.
If your Python installation is not configured to be the default Python installation (this is common with Anaconda
and PortablePython distributions) you will need to set the Python executable in your config file
[PYTHON]
executable = <path to your python.exe>
13
PyXLL User Guide, Release 4.2.4
DLL not found
If you get an error saying that Python is not installed or the Python dll can’t be found you may need to set the Python
executable in the config.
If setting the executable doesn’t resolve the problem then it’s possible your Python dll is in a non-standard location.
You can also set the dll location in the config to tell PyXLL where to find it.
4. Install the addin Once you’re happy with the configuration you can install the addin in Excel by following the
instructions below.
• Excel 2010 - 2019 / Office 365 Select the File menu in Excel and go to Options -> Add-Ins -> Man-
age Excel Addins and browse for the folder you unpacked PyXLL to and select pyxll.xll.
• Excel 2007 Click the large circle at the top left of Excel and go to Options -> Add-Ins -> Manage
Excel Addins and browse for the folder you unpacked PyXLL to and select pyxll.xll.
• Excel 97 - 2003 Go to Tools -> Add-Ins -> Browse and locate pyxll.xll in the folder you unpacked
the zip file to.
5. Optional Install the PyXLL stubs package If you are using a Python IDE that provides autocompletion or
code checking, because the pyxll module isn’t installed, you may find it complains and won’t be able to
provide any autocompletion for functions imported from the pyxll module.
In the downloaded zip file you will find a .whl file whose exact filename depends on the version of PyXLL.
That’s a Python Wheel containing a dummy pyxll module that you can import when testing without
PyXLL. You can then use code that depends on the pyxll module outside of Excel (e.g. when unit testing).
To install the wheel run the following command (substituting the actual wheel filename) from a command
line:
> pip install {pyxll wheel filename}
The real pyxll module is compiled into the pyxll.xll addin, and so is always available when your code is
running inside Excel.
3.1. Installing the PyXLL Excel Add-In 14
PyXLL User Guide, Release 4.2.4
If you are using a version of Python that doesn’t support pip you can instead simply unzip the .whl file into
your Python site-packages folder (the wheel file is simply a zip file with a different file extension).
3.2 Configuring PyXLL
Finding the config file
In PyXLL’s About dialog it displays the full path to the config file in use. Clicking on the path will open the config
file in your default editor.
The PyXLL config is available to your addin code at run-time via get_config.
If you add your own sections to the config file they will be ignored by PyXLL but accessible to your code via the
config object.
• Introduction
• Environment Variables in Config values
• Python Settings
• PyXLL Settings
• Logging
• Environment Variables
• Menu Ordering
• Shortcuts
• License Key
3.2.1 Introduction
The config file is a plain text file that should be kept in the same folder as the PyXLL addin .xll file, and should have
the same name as the addin but with a .cfg extension. In most cases it will simply be pyxll.cfg.
To load the config file from an alternative location, set the environment variable PYXLL_CONFIG_FILE to the full
path of the config file you wish to load before starting Excel.
Paths used in the config file may be absolue or relative paths. Relative paths are interpreted relative to the directory
containing the config file.
3.2.2 Environment Variables in Config values
Config values may contain environment variable substitutions. To substitute an environment variable into your value
use %(ENVVAR_NAME)s. Default values, for use when the variable has not bee set, can be provided using the format
%(ENVVAR_NAME:default_value)s (new in PyXLL 4.1), e.g.
[LOG]
path = %(TEMP:./logs)s
file = %(LOG_FILE:pyxll.log)s
3.2. Configuring PyXLL 15
PyXLL User Guide, Release 4.2.4
3.2.3 Python Settings
[PYTHON]
pythonpath = semi-colon or new line delimited list of directories
executable = full path to the Python executable (python.exe)
dll = full path to the Python dynamic link library (pythonXX.dll)
pythonhome = location of the standard Python libraries
ignore_environment = ignore environment variables when initializing Python
The Python settings determine which Python interpreter will be used, and some Python settings.
If you don’t specify anything in the Python section then your system default Python settings will be used. Depending
on how Python was installed on your system this may be fine, but sometimes you may want to specify different options
from your system default; for example, when using a Python virtual environment or if the Python you want to use is
not installed as your system default Python.
• pythonpath The pythonpath is a list of directories that Python will search in when importing modules.
When writing your own code to be used with PyXLL you will need to change this to include the directories
where that code can be imported from.
[PYTHON]
pythonpath =
c:\path\to\your\code
c:\path\to\some\more\of\your\code
.\relative\path\relative\to\config\file
• executable If you want to use a different version of Python than your system default Python then setting
this option will allow you to do that.
Note that the Python version (e.g. 2.7 or 3.5) must still match whichever Python version you selected when
downloading PyXLL, but this allows you to switch between different virtual environments or different
Python distributions.
PyXLL does not actually use the executable for anything, but this setting tells PyXLL where it can expect
to find the other files it needs as they will be installed relative to this file (e.g. the Python dll and standard
libraries).
[PYTHON]
executable = c:\path\to\your\python\installation\pythonw.exe
If you wish to set the executable globally outside of the config file, the environment variable
PYXLL_PYTHON_EXECUTABLE can be used. The value set in the config file is used in preference over
this environment variable.
• dll Usually setting the Python executable will be enough so that PyXLL can find the dll without further help,
but if your installation is non-standard or you need to tell PyXLL to use a specific dll for any reason then
this setting may be used for that.
[PYTHON]
dll = c:\path\to\your\python\installation\pythonXX.dll
If you wish to set the dll globally outside of the config file, the environment variable
PYXLL_PYTHON_DLL can be used. The value set in the config file is used in preference over this envi-
ronment variable.
• pythonhome The location of the standard libraries will usually be determined from with the system default
Python installation or by looking for them relative to the Python executable.
3.2. Configuring PyXLL 16
PyXLL User Guide, Release 4.2.4
If for any reason the standard libraries are not installed relative to the chosen Python executable then setting
this option will tell PyXLL where to find them.
Usually if this setting is set at all it should be set to whatever sys.prefix evaluates to in a Python
prompt.
[PYTHON]
pythonhome = c:\path\to\your\python\installation
If you wish to set the pythonhome globally outside of the config file, the environment variable
PYXLL_PYTHONHOME can be used. The value set in the config file is used in preference over this envi-
ronment variable.
• ignore_environment New in PyXLL 3.5
When set any standard Python environment variables such as PYTHONPATH are ignored when initializing
Python.
This is advisable so that any global environment variables that might conflict with the settings in the
pyll.cfg file do not affect how Python is initialized.
This must be set if using FINCAD, as FINCAD sets PYTHONPATH to it’s own internal Python distribu-
tion.
3.2.4 PyXLL Settings
[PYXLL]
modules = comma or new line delimited list of python modules
ribbon = filename of a ribbon xml document
developer_mode = 1 or 0 indicating whether or not to use the developer mode
name = name of the addin visible in Excel
auto_resize_arrays = 1 or 0 to enable automatic resizing of all array functions
error_handler = function for handling uncaught exceptions
external_config = paths of additional config files to load
# reload settings
deep_reload = 1 or 0 to activate or deactivate the deep reload feature
deep_reload_include = modules and packages to include when reloading (only when deep_
˓→reload is set)
deep_reload_exclude = modules and packages to exclude when reloading (only when deep_
˓→reload is set)
deep_reload_disable = 1 or 0 to disable all deep reloading functionality
# allow abort settings
allow_abort = 1 or 0 to set the default value for the allow_abort kwarg
abort_throttle_time = minimum time in seconds between checking abort status
abort_throttle_count = minimum number of calls to trace function between checking
˓→abort status
# advanced settings
always_use_2d_arrays = disable 1d array types and use ``[]`` to mean a 2d array
get_cached_object_id = function to get the id to use for cached objects
clear_object_cache_on_reload = clear the object cache when reloading PyXLL
error_cache_size = maximum number of exceptions to cache for failed function calls
disable_com_addin = 1 or 0 to disable the COM addin component of PyXLL
quiet = 1 or 0 to disable all start up messages
# asyncio event loop settings
3.2. Configuring PyXLL 17
PyXLL User Guide, Release 4.2.4
stop_event_loop_on_reload = 1 or 0 to stop the event loop when reloading PyXLL
start_event_loop = fully qualified function name if providing your own event loop
stop_event_loop = fully qualified function name to stop the event loop
• modules When PyXLL starts or is reloaded this list of modules will be imported automatically.
Any code that is to be exposed to Excel should be added to this list, or imported from modules in this list.
The locations of these modules must be on the pythonpath, which can be set in the [PYTHON] config
section.
• ribbon If set, the ribbon setting should be the file name of custom ribbon user interface XML file. The file
name may be an absolute path or relative to the config file.
The XML file should conform to the Microsoft CustomUI XML schema (customUI.xsd) which may be
downloaded from Microsoft here https://www.microsoft.com/en-gb/download/details.aspx?id=1574.
See the Customizing the Ribbon chapter for more details.
• developer_mode When the developer mode is active a PyXLL menu with a Reload menu item will be
added to the Addins toolbar in Excel.
If the developer mode is inactive then no menu items will be automatically created so the only ones visible
will be the ones declared in the imported user modules.
This setting defaults to off (0) if not set.
• name The name setting, if set, changes the name of the addin as it appears in Excel.
When using this setting the addin in Excel is indistinguishable from any other addin, and there is no
reference to the fact it was written using PyXLL. If there are any menu items in the default menu, that
menu will take the name of the addin instead of the default ‘PyXLL’.
• auto_resize_arrays The auto_resize_arrays setting can be used to enable automatic resizing of array
formulas for all array function. It is equivalent to the auto_resize keyword argument to xl_func and
applies to all array functions that don’t explicitly set auto_resize.
It should be set to 1 for True or 0 for False. If unset the default is 0.
• error_handler If a worksheet function raises an uncaught exception, the error handler specified here will
be called and the result of the error handler is returned to Excel.
If not set, uncaught exceptions are returned to Excel as error codes.
See Error Handling.
• external_config This setting may be used to reference another config file (or files) located elsewhere.
For example, if you want to have the main pyxll.cfg installed on users’ local PCs but want to control the
configuration via a shared file on the network you can use this to reference that external config file.
Multiple external config files can be used by setting this value to a list of file names (comma or newline
separated) or file patterns.
Values in external config files override what’s in the parent config file, apart from pythonpath, modules and
external_config which get appended to.
In addition to setting this in the config file, the environment variable PYXLL_EXTERNAL_CONFIG_FILE
can be used. Any external configs set by this environment variable will be added to those specified in the
config.
• deep_reload Reloading PyXLL reloads all the modules listed in the modules config setting. When working
on more complex projects often you need to make changes not just to those modules, but also to modules
imported by those modules.
3.2. Configuring PyXLL 18
PyXLL User Guide, Release 4.2.4
PyXLL keeps track of anything imported by the modules listed in the modules config setting (both imported
directly and indirectly) and when the deep_reload feature is enabled it will automatically reload the module
dependencies prior to reloading the main modules.
Standard Python modules and any packages containing C extensions are excluded from being reloaded.
This setting defaults to off (0) if not set.
• deep_reload_include Optional list of modules or packages to restrict reloading to when deep reloading
is enabled.
If not set, everything excluding the standard Python library and packages with C extensions will be con-
sidered for reloading.
This can be useful when working with code in only a few packages, and you don’t want to reload everything
each time you reload. For example, you might have a package like:
my_package \
- __init__.py
- business_logic.py
- data_objects.py
- pyxll_functions.py
In your config you would add my_package.pyxll_function to the modules to import, but when reload-
ing you would like to reload everything in my_package but not any other modules or packages that it
might also import (either directly or indirectly). By adding my_package to deep_reload_include the deep
reloading is restricted to only reload modules in that package (in this case, my_package.business_logic and
my_package.data_objects).
[PYXLL]
modules = my_package
deep_reload = 1
deep_reload_include = my_package
• deep_reload_exclude Optional list of modules or packages to exclude from deep reloading when
deep_reload is set.
If not set, only modules in the standard Python library and modules with C extensions will be ignored
when doing a deep reload.
Reloading Python modules and packages doesn’t work for all modules. For example, if a module modifies
the global state in another module when its imported, or if it contains a circular dependency, then it can be
problematic trying to reload it.
Because the deep_reload feature will attempt to reload all modules that have been imported, if you have a
module that cannot be reloaded and is causing problems it can be added to this list to be ignored.
Excluding a package (or sub-package) has the effect of ignoring anything within that package or sub-
package. For example, if there are modules ‘a.b’ and ‘a.c’ then excluding ‘a’ will also exclude ‘a.b’ and
‘a.c’.
deep_reload_exclude can be set when deep_reload_include is set to restrict the set of modules that
will be reloaded. For example, if there are modules ‘a.b and ‘a.b.c’, and everything in ‘a’ should be
reloaded except for ‘a.b.c’ then ‘a’ would be added to deep_reload_include and ‘a.b.c’ would be added to
deep_reload_exclude.
• deep_reload_disable Deep reloading works by installing an import hook that tracks the dependencies
between imported modules. Even when deep_reload is turned off this import hook is enabled, as it is
sometimes convenient to be able to turn it on to do a deep reload without restarting Excel.
3.2. Configuring PyXLL 19
PyXLL User Guide, Release 4.2.4
When deep_reload_disable is set to 1 then this import hook is not enabled and setting deep_reload will
have no effect.
Changing this setting requires Excel to be restarted.
• allow_abort The allow_abort setting is optional and sets the default value for the allow_abort keyword
argument to the decorators xl_func, xl_macro and xl_menu.
It should be set to 1 for True or 0 for False. If unset the default is 0.
Using this feature enables a Python trace function which will impact the performance of Python code while
running a UDF. The exact performance impact will depend on what code is being run.
• abort_throttle_time When a UDF has been registered as abort-able, a trace function is used that gets
called frequently as the Python code is run by the Python interpreter.
To reduce the impact of the trace function Excel can be queried less often to see if the user has aborted the
function.
abort_throttle_time is the minimum time in seconds between checking Excel for the abort status.
• abort_throttle_count When a UDF has been registered as abort-able, a trace function is used that gets
called frequently as the Python code is run by the Python interpreter.
To reduce the impact of the trace function Excel can be queried less often to see if the user has aborted the
function.
abort_throttle_count is the minimum number of call to the trace function between checking Excel for the
abort status.
• always_use_2d_arrays Before PyXLL 4.0, all array arguments and return types were 2d arrays (list of
lists). The type suffix [] was used to mean a 2d array type (e.g. a float[] argument would receive a
list of lists).
Since PyXLL 4.0, 1d arrays have been added and [][] should now be used when a 2d array is required.
To make upgrading easier, this setting disables 1d arrays and any array types specified with [] will be 2d
arrays as they were prior to version 4.
• get_cached_object_id When Python objects are returned from an Excel worksheet function and no
suitable converter is found (or the return type object is specified) the object is added to an internal
object cache and a handle to that cached object is returned.
The format of the cached object handle can be customized by setting get_cached_object_id to a custom
function, e,g:
[PYXLL]
get_cached_object_id = module_name.get_custom_object_id
def get_custom_object_id(obj):
return "[Cached %s <0x%x>]" % (type(obj), id(obj))
The computed id must be unique as it’s used when passing these objects to other functions, which retrieves
them from the cache by the id.
• clear_object_cache_on_reload Clear the object cache when reloading the PyXLL add-in.
Defaults to 1, but if using cached objects that are instances of classes that aren’t reloaded then this can be
set to 0 to avoid having to recreate them when reloading.
• error_cache_size If a worksheet function raises an uncaught exception it is cached for retrieval via the
get_last_error function.
3.2. Configuring PyXLL 20
PyXLL User Guide, Release 4.2.4
This setting sets the maximum number of exceptions that will be cached. The least recently raised excep-
tions are removed from the cache when the number of cached exceptions exceeds this limit.
The default is 500.
• disable_com_addin PyXLL is packaged as a single Excel addin (the pyxll.xll file), but it actually imple-
ments both a standard XLL addin and COM addin in the same file.
Setting disable_com_addin to 1 stops the COM addin from being used.
The COM addin is used for ribbon customizations and RTD functions and if disabled these features will
not be available.
• quiet The quiet setting is for use in enterprise settings where the end user has no knowledge that the functions
they’re provided with are via a PyXLL addin.
When set PyXLL won’t raise any message boxes when starting up, even if errors occur and the addin can’t
load correctly. Instead, all errors are written to the log file.
• stop_event_loop_on_reload If set to ‘1’, the asyncio Event Loop used for async user defined functions
and RTD methods will be stopped when PyXLL is reloaded.
See Asynchronous Functions.
New in PyXLL 4.2.0.
• start_event_loop Used to provide an alternative implementation of the asyncio event loop used by
PyXLL.
May be set to the fully qualified name of a function that takes no arguments and returns a started asyn-
cio.AbstractEventLoop.
If this option is set then stop_event_loop should also be set.
See Asynchronous Functions.
New in PyXLL 4.2.0.
• stop_event_loop Used to provide an alternative implementation of the asyncio event loop used by PyXLL.
May be set to the fully qualified name of a function that stops the event loop started by the function
specified by the option start_event_loop.
If this option is set then start_event_loop should also be set.
See Asynchronous Functions.
New in PyXLL 4.2.0.
3.2.5 Logging
PyXLL redirects all stdout and stderr to a log file. All logging is done using the standard logging python module.
The [LOG] section of the config file determines where logging information is redirected to, and the verbosity of the
information logged.
[LOG]
path = directory of where to write the log file
file = filename of the log file
format = format string
verbosity = logging level (debug, info, warning, error or critical)
encoding = encoding to use when writing the logfile (defaults to 'utf-8')
3.2. Configuring PyXLL 21
PyXLL User Guide, Release 4.2.4
PyXLL creates some configuration substitution values that are useful when setting up logging.
Substitution Variable Description
pid process id
date current date
xlversion Excel version
• path Path where the log file will be written to.
This may include substitution variables as listed above, e.g.
[LOG]
path = C:/Temp/pyxll-logs-%(date)s
• file Filename of the log file.
This may include substitution variables as listed above, e.g.
[LOG]
file = pyxll-log-%(pid)s-%(xlversion)s-%(date)s.log
• format The format string is used by the logging module to format any log messages. An example format
string is:
[LOG]
format = "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
For more information about log formatting, please see the logging module documentation from the
Python standard library.
• verbosity The logging verbosity can be used to filter out or show warning and errors. It sets the log level
for the root logger in the logging module, as well as setting PyXLL’s internal log level.
It may be set to any of the following
– debug (most verbose level, show all log messages including debugging messages)
– info
– warning
– error
– critical (least verbose level, only show the most critical errors)
If you are having any problems with PyXLL it’s recommended to set the log verbosity to debug as that
will give a lot more information about what PyXLL is doing.
• encoding Encoding to use when writing the log file.
Defaults to ‘utf-8’.
New in PyXLL 4.2.0.
3.2.6 Environment Variables
For some python modules it can be helpful to set some environment variables before they are imported. Usually this
would be done in the environment running the python script, but in Excel it’s more complicated as it would require
either changing the global environment variables on each PC, or using a batch script to launch Excel.
For this reason, it’s possible to set environment variables in the [ENVIRONMENT] section of the config file.
3.2. Configuring PyXLL 22
PyXLL User Guide, Release 4.2.4
[ENVIRONMENT]
NAME = VALUE
For each environment variable you would like set, add a line to the [ENVIRONMENT] section.
3.2.7 Menu Ordering
Menu items added via the xl_menu decorator can specify what order they should appear in the menus. This can be
also be set, or overridden, in the config file.
To specify the order of sub-menus and items within the sub-menus use a “.” between the menu name, sub-menu name
and item name.
The example config below shows how to order menus with menu items and sub-menus.
[MENUS]
menu_1 = 1 # order of the top level menu menu_1
menu_1.menu_item_1 = 1 # order of the items within menu_1
menu_1.menu_item_2 = 2
menu_1.menu_item_3 = 3
menu_2 = 2 # order of the top level menu menu_2
menu_2.sub_menu_1 = 1 # order of the sub-menu sub_menu_1 within menu_2
menu_2.sub_menu_1.menu_item_1 = 1 # order of the items within sub_menu_1
menu_2.sub_menu_1.menu_item_2 = 2
menu_2.menu_item_1 = 2 # order of item within menu_2
menu_2.sub_menu_2 = 3
menu_2.sub_menu_2.menu_item_1 = 1
menu_2.sub_menu_2.menu_item_2 = 2
Here’s how the menus appear in Excel:
3.2. Configuring PyXLL 23
PyXLL User Guide, Release 4.2.4
3.2.8 Shortcuts
Macros can have keyboard shortcuts assigned to them by using the shortcut keyword argument to xl_macro. Alter-
natively, these keyboard shortcuts can be assigned, or overridden, in the config file.
Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For
example, ‘Ctrl+Shift+R’. If the same key combination is already in use by Excel it may not be possible to assign a
macro to that combination.
The PyXLL developer macros (reload and rebind) can also have shortcuts assigned to them.
[SHORTCUTS]
pyxll.reload = Ctrl+Shift+R
module.macro_function = Alt+F3
See Keyboard Shortcuts for more details.
3.2.9 License Key
[LICENSE]
key = license key
file = path to shared license key file
If you have a PyXLL license key you should set it in [LICENSE] section of the config file.
The license key may be embedded in the config as a plain text string, or it can be referenced as an external file
containing the license key. This can be useful for group licenses so that the license key can be managed centrally
without having to update each user’s configuration when it is renewed.
• key Plain text license key as provided when you purchased PyXLL.
This does not need to be set if you are setting file.
• file Path of a plain text file containing the license key as provided when you purchased PyXLL. The file may
contain comment lines starting with #.
This does not need to be set if you are setting key.
3.3 Worksheet Functions (UDFs)
• Introduction
• Argument Types and Return Types
– Basic Types
– Array Types
– Dictionary Types
– NumPy Array Types
– Pandas Types
– Cached Python Objects
– Custom Types
3.3. Worksheet Functions (UDFs) 24
PyXLL User Guide, Release 4.2.4
– Manual Type Conversion
• Default Arguments
• Error Handling
– Exceptions raised by a UDF
– Passing Errors as Values
– Retrieving Error Information
• Resizing Array Formulas
• Documenting Functions
• Functions with a Variable Number of Arguments
• Interrupting Functions
3.3.1 Introduction
Tip: You don’t have to restart Excel
Use the ‘Reload’ menu item under the PyXLL menu to reload your Python code without restarting Excel
Calling user defined functions (UDFs) written in Python using PyXLL in Excel is exactly the same as calling any other
Excel worksheet function written in VBA or as part of another Excel addin. They are called from formulas in an Excel
worksheet in the same way, and appear in Excel’s function wizard.
Here’s a simple example of a worksheet function written in Python
from pyxll import xl_func
@xl_func
def hello(name):
return "Hello, %s" % name
The decorator xl_func tells PyXLL to register that Python function as a worksheet function in Excel.
Once that code is saved it can be added to the pyxll.cfg config file:
[PYXLL]
modules = <add your new module here>
[PYTHON]
pythonpath = <add the folder containing your Python code here>
When you reload the PyXLL addin or restart Excel the function you have just added will be available to use in a
formula in an Excel worksheet.
=hello("me")
3.3. Worksheet Functions (UDFs) 25
PyXLL User Guide, Release 4.2.4
If you’ve not installed the PyXLL addin yet, see quickstart3.
Worksheet functions can take simple values, as in the example above, or more complex arguments such as arrays of
data.
PyXLL has a type system that makes it easy for arguments passed to your Python functions or returned to Excel to be
converted to and from Excel’s data types to the most appropriate Python data types.
Examples of more complex types supported by PyXLL include NumPy arrays, Pandas DataFrames and Series and
Python objects. Support for other types can be added via PyXLL’s custom type system.
In order for PyXLL to apply to correct type conversion the Python function must have a function signature.
3.3.2 Argument Types and Return Types
• Basic Types
• Array Types
• Dictionary Types
• NumPy Array Types
• Pandas Types
• Cached Python Objects
• Custom Types
3.3. Worksheet Functions (UDFs) 26
PyXLL User Guide, Release 4.2.4
• Manual Type Conversion
When a Python function is registered in Excel it’s possible to specify what types the arguments to that function are
expected to be, and what the return type is.
There are a few different ways to do this, but the most common is to provide a function signature as the first argument
to xl_func:
from pyxll import xl_func
from datetime import date, timedelta
@xl_func("date d, int i: date")
def add_days(d, i):
return d + timedelta(days=i)
When adding a function signature string it is written as a comma separated list of each argument type followed by the
argument name, ending with a colon followed by the return type.
Adding type information is useful as it means that any necessary type conversion can be done automatically before
your function is called.
Type information can also be provided using type annotations in Python 3:
from pyxll import xl_func
from datetime import date, timedelta
@xl_func
def add_days(d: date, i: int) -> date:
return d + timedelta(days=i)
The examples above show how date can be passed to Python functions from Excel. Internally, an Excel date is just
a number. If you call a function with no type information with a date then that argument will just be a floating point
number when it is passed to your Python function. Rather than convert from a float to a date in every function that
expects a date you can annotate your Python function to tell PyXLL and Excel what type you expect and have the
conversion done automatically.
The final way type information can be added to a function is by using the xl_arg and xl_return decorators.
These are particularly useful for more complex types that require parameters, such as NumPy arrays and Pandas
types. Parameterized types can be specified as part of the function signature, or using xl_arg and xl_return.
For example, the following function takes two 1-dimensional NumPy arrays, using a function signature:
from pyxll import xl_func
import numpy as np
@xl_func("numpy_array<ndim=1> a, numpy_array<ndim=1> b: var")
def add_days(a, b):
return np.correlate(a, b)
But this could be re-written using xl_arg as follows:
from pyxll import xl_func, xl_arg
import numpy as np
@xl_func
@xl_arg("a", "numpy_array", ndim=1)
@xl_arg("b", "numpy_array", ndim=1)
def add_days(a, b):
return np.correlate(a, b)
3.3. Worksheet Functions (UDFs) 27
PyXLL User Guide, Release 4.2.4
Basic Types
Several standard types may be used in the signature specifed when exposing a Python worksheet function. It is also
possible to pass arrays and more complex objects, which are discussed later.
Below is a list of the basic types. Any of these can be specified as an argument type or return type in a function
signature.
PyXLL type Python type
bool bool
datetime datetime.datetime
1
date datetime.date
float float
int int
object object
2
rtd RTD
3
str str
time datetime.time
unicode unicode
4
var object
5
xl_cell XLCell
6
Notes
Array Types
See arrayfuncs for more details about array functions.
Ranges of cells can be passed from Excel to Python as a 1d or 2d array.
Any type can be used as an array type by appending [] for a 1d array or [][] for a 2d array:
from pyxll import xl_func
@xl_func("float[][] array: float")
def py_sum(array):
"""return the sum of a range of cells"""
1
Excel represents dates and times as numbers. PyXLL will convert dates and times to and from Excel’s number representation, but in Excel they
will look like numbers unless formatted. When returning a date or time from a Python function you will need to change the Excel cell formatting to
a date or time format.
2
The object type in PyXLL passes Python objects between functions as object handles that reference the real objects in an internal object
cache.
For primitive types, use the var type instead.
3
rtd is for functions that return Real Time Data.
4
Unicode was only introduced in Excel 2007 and is not available in earlier versions. Use xl_version to check what version of Excel is being
used if in doubt.
5
The var type can be used when the argument or return type isn’t fixed. Using the strong types has the advantage that arguments passed from
Excel will get coerced correctly. For example if your function takes an int you’ll always get an int and there’s no need to do type checking in your
function. If you use a var, you may get a float if a number is passed to your function, and if the user passes a non-numeric value your function
will still get called so you need to check the type and raise an exception yourself.
If no type information is provided for a function it will be assumed that all arguments and the return type are the var type.
6
Specifying xl_cell as an argument type passes an XLCell instance to your function instead of the value of the cell. This is useful if you
need to know the location or some other data about the cell used as an argument as well as its value.
3.3. Worksheet Functions (UDFs) 28
PyXLL User Guide, Release 4.2.4
total = 0.0
# 2d array is a list of lists of floats
for row in array:
for cell_value in row:
total += cell_value
return total
A 1d array is represented in Python as a simple list, and when a simple list is returned to Excel it will be returned as a
column of data. A 2d array is a list of lists (list of rows) in Python. To return a single row of data, return it as a two 2d
list of lists with only a single row.
When returning a 2d array remember that it has to be a list of lists. This means to return a row of data you would
return [[1,2,3,4]], for example. To enter an array forumla in Excel you select the cells, enter the formula and
then press Ctrl+Shift+Enter.
Any type can be used as an array type, but float[] and float[][] require the least marshalling between Excel
and python and are therefore the fastest of the array types.
If you a function argument has no type specified or is using the var type, if it is passed a range of data that will be
converted into a 2d list of lists of values and passed to the Python function.
Dictionary Types
Python functions can be passed a dictionary, converted from an Excel range of values.
The following is a simple function that accepts an dictionary of integers keyed by strings. Note that the key and value
types are optional and default to var if not specified.
@xl_func("dict<str, int>: str")
def dict_test(x):
return str(x)
The dict type can be parameterized so that you can also specify the key and value types, and some other options.
• dict, when used as an argument type
dict<key=var, value=var, transpose=False, ignore_missing_keys=True>
– key Type used for the dictionary keys.
– value Type used for the dictionary values.
– transpose - False: Expect the dictionary with the keys on the first column of data and the values
on the second. - True: Expect the dictionary with the keys on the first row of data and the values on
the second. - None: Try to infer the orientation from the data passed to the function.
– ignore_missing_keys If True, ignore any items where the key is missing.
3.3. Worksheet Functions (UDFs) 29
PyXLL User Guide, Release 4.2.4
• dict, when used as an return type
dict<key=var, value=var, transpose=False, order_keys=True>
– key Type used for the dictionary keys.
– value Type used for the dictionary values.
– transpose - False: Return the dictionary with the keys on the first column of data and the values
on the second. - True: Return the dictionary with the keys on the first row of data and the values on
the second.
– order_keys Sort the dictionary by its keys before returning it to Excel.
NumPy Array Types
To be able to use numpy arrays you must have numpy installed and in your pythonpath.
You can use numpy 1d and 2d arrays as argument types to pass ranges of data into your function, and as return types
for returing for array functions. Only up to 2d arrays are supported, as higher dimension arrays don’t fit well with how
data is arranged in a spreadsheet.
To specify that a function should accept a numpy array as an argument or as its return type, use the numpy_array,
numpy_row or numpy_column types in the function signature used with xl_func.
These types can be parameterized, meaning you can set some additional options when specifying the type in the
function signature.
numpy_array<dtype=float, ndim=2, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• dim Array dimensions, must be 1 or 2.
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
numpy_row<dtype=float, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
numpy_column<dtype=float, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
For example, a function accepting two 1d numpy arrays of floats and returning a 2d array would look like:
3.3. Worksheet Functions (UDFs) 30
PyXLL User Guide, Release 4.2.4
from pyxll import xl_func
import numpy
@xl_func("numpy_array<float, ndim=1> a, numpy_array<float, ndim=1> b: numpy_array
˓→<float>")
def numpy_outer(a, b):
return numpy.outer(a, b)
The ‘float’ dtype isn’t strictly necessary as it’s the default. If you don’t want to set the type parameters in the signature,
the xl_arg and xl_return decorators can be used instead.
PyXLL can automatically resize the range of the array formula to match the returned data by setting
auto_resize=True in xl_func.
Floating point numpy arrays are the fastest way to get data out of Excel into Python. If you are working on perfor-
mance sensitive code using a lot of data, try to make use of numpy_array<float> or numpy_array<float,
casting='nan'> for the best performance.
See arrayfuncs for more details about array functions.
Pandas Types
Pandas DataFrames and Series can be used as function arguments and return types for Excel worksheet functions.
When used as an argument, the range specified in Excel will be converted into a Pandas DataFrame or Series as
specified by the function signature.
When returning a DataFrame or Series, a range of data will be returned to Excel. PyXLL can automatically resize the
range of the array formula to match the returned data by setting auto_resize=True in xl_func.
The following shows returning a random dataframe, including the index:
from pyxll import xl_func
import pandas as pd
import numpy as np
@xl_func("int rows, int columns: dataframe<index=True>", auto_resize=True)
def random_dataframe(rows, columns):
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
The following options are available for the dataframe and series argument and return types:
• dataframe, when used as an argument type
dataframe<index=0, columns=1, dtype=None, dtypes=None, index_dtype=None>
index Number of columns to use as the DataFrame’s index. Specifying more than one will result in a
DataFrame where the index is a MultiIndex.
columns Number of rows to use as the DataFrame’s columns. Specifying more than one will result in a
DataFrame where the columns is a MultiIndex. If used in conjunction with index then any column headers
on the index columns will be used to name the index.
dtype Datatype for the values in the dataframe. May not be set with dtypes.
dtypes Dictionary of column name -> datatype for the values in the dataframe. May not be set with dtype.
index_dtype Datatype for the values in the dataframe’s index.
3.3. Worksheet Functions (UDFs) 31
PyXLL User Guide, Release 4.2.4
• dataframe, when used as a return type
dataframe<index=None, columns=True>
index If True include the index when returning to Excel, if False don’t. If None, only include if the index is
named.
columns If True include the column headers, if False don’t.
• series, when used as an argument type
series<index=1, transpose=None, dtype=None, index_dtype=None>
index Number of columns (or rows, depending on the orientation of the Series) to use as the Series index.
transpose Set to True if the Series is arranged horizontally or False if vertically. By default the orientation
will be guessed from the structure of the data.
dtype Datatype for the values in the Series.
index_dtype Datatype for the values in the Series’ index.
• series, when used as a return type
series<index=True, transpose=False>
index If True include the index when returning to Excel, if False don’t.
transpose Set to True if the Series should be arranged horizontally, or False if vertically.
When passing large DataFrames between Python functions, it is not always necessary to return the full DataFrame to
Excel and it can be expensive reconstructing the DataFrame from the Excel range each time. In those cases you can
use the object return type to return a handle to the Python object. Functions taking the dataframe and series
types can accept object handles.
See pandas for more information.
Cached Python Objects
Not all Python types can be conveniently converted to a type that can be represented in Excel.
Even for types that can be represented in Excel it is not always desirable to do so (for example, and Pandas DataFrame
with millions of rows could be returned to Excel as a range of data, but it would not be very useful and would make
Excel very slow).
For cases like these, PyXLL can return a handle to the Python object to Excel instead of trying to convert the object to
an Excel friendly representation. This allows for Python objects to be passed between Excel functions easily, without
the complexity or possible performance problems of converting them between the Python and Excel representations.
The following example shows one function that returns a Python object, and another that takes that Python object as
an argument:
from pyxll import xl_func
class CustomObject:
def __init__(self, name):
self.name = name
@xl_func("string name: object")
def create_object(x):
return CustomObject(x)
@xl_func("object x: string")
3.3. Worksheet Functions (UDFs) 32
PyXLL User Guide, Release 4.2.4
def get_object_name(x):
assert isinstance(x, CustomObject)
return x.name
Note that the object is not copied. This means if you modify the object passed to your function then you will be
modifying the object in the cache.
When using the var type, if an object of a type that has no converter is returned then the object type is used.
When an object is returned in this way it is added to an internal object cache. This cache is managed by PyXLL so
that objects are evicted from the cache when they are no longer needed.
When Excel first starts, or when PyXLL is reloaded, the cache is empty and so functions returning objects must be run
to populate the cache. The easiest way to ensure all required cached objects have been created is to fully recalculate
by pressing Ctrl+Alt+F9.
To fetch an object from the cache by its handle, get_type_converter can be used, e.g.:
from pyxll import xl_func, get_type_converter
@xl_func("str handle: object")
def check_object_handle(handle):
get_cached_object = get_type_converter("str", "object")
obj = get_cached_object(handle)
return obj
The method of generating object handles can be customized by setting get_cached_object_id in the PYXLL
section of the config file.
3.3. Worksheet Functions (UDFs) 33
PyXLL User Guide, Release 4.2.4
Custom Types
As well as the standard types listed above, it’s also possible to define your own argument and return types that can
then be used in your function signatures.
Custom argument types need a function that will convert a standard type to the custom type, which will then be passed
to your function. For example, if you have a function that takes an instance of type X, you can declare a function to
convert from a standard type to X and then use X as a type in your function signature. When called from Excel, your
conversion function will be called with an instance of the base type, and then your exposed UDF will be called with
the result of that conversion.
To declare a custom type, you use the xl_arg_type decorator on your conversion function. The xl_arg_type
decorator takes at least two arguments, the name of your custom type and the base type.
Here’s an example of a simple custom type:
from pyxll import xl_arg_type, xl_func
class CustomType:
def __init__(self, x):
self.x = x
@xl_arg_type("CustomType", "string")
def string_to_customtype(x):
return CustomType(x)
@xl_func("CustomType x: bool")
def test_custom_type_arg(x):
# this function is called from Excel with a string, and then
# string_to_customtype is called to convert that to a CustomType
# and then this function is called with that instance
return isinstance(x, CustomType)
CustomType can now be used as an argument type in a function signature. The Excel UDF will take a string, but before
your Python function is called the conversion function will be used to convert that string to a CustomType instance.
To use a custom type as a return type you also have to specify the conversion function from your custom type to a base
type. This is exactly the reverse of the custom argument type conversion described previously.
The custom return type conversion function is decorated with the xl_return_type decorator.
For the previous example the return type conversion function could look like:
from pyxll import xl_return_type, xl_func
@xl_return_type("CustomType", "string")
def customtype_to_string(x):
# x is an instance of CustomType
return x.x
@xl_func("string x: CustomType")
def test_returning_custom_type(x):
# the returned object will get converted to a string
# using customtype_to_string before being returned to Excel
return CustomType(x)
Any recognized type can be used as a base type. That can be a standard type, an array type or another custom type (or
even an array of a custom type!). The only restriction is that it must resolve to a standard type eventually.
3.3. Worksheet Functions (UDFs) 34
PyXLL User Guide, Release 4.2.4
Custom types can be parameterized by adding additional keyword arguments to the conversion functions. Val-
ues for these arguments are passed in from the type specification in the function signature, or using xl_arg and
xl_return:
from pyxll import xl_arg_type, xl_func
class CustomType2:
def __init__(self, x, y):
self.x = x
self.y = y
@xl_arg_type("CustomType2", "string", y=None)
def string_to_customtype2(x):
return CustomType(x, y)
@xl_func("CustomType2<y=1> x: bool")
def test_custom_type_arg2(x):
assert x.y == 1
return isinstance(x, CustomType)
Manual Type Conversion
Sometimes it’s useful to be able to convert from one type to another, but it’s not always convenient to have to determine
the chain of functions to call to convert from one type to another.
For example, you might have a function that takes an array of var types, but some of those may actually be datetimes,
or one of your own custom types.
To convert them to those types you would have to check what type has actually been passed to your function and then
decide what to call to get it into exactly the type you want.
PyXLL includes the function get_type_converter to do this for you. It takes source and target types by name
and returns a function that will perform the conversion, if possible.
Here’s an example that shows how to get a datetime from a var parameter:
from pyxll import xl_func, get_type_converter
from datetime import datetime
@xl_func("var x: string")
def var_datetime_func(x):
var_to_datetime = get_type_converter("var", "datetime")
dt = var_to_datetime(x)
# dt is now of type 'datetime'
return "%s : %s" % (dt, type(dt))
3.3.3 Default Arguments
Python functions registered with xl_func can have default argument and variable *args arguments. Any arguments
not specified when called from Excel will use their default value.
It is not necessary to do any specified in the function signature. It’s sufficient to declare the function with its default
arguments as you normally would in Python:
from pyxll import xl_func
3.3. Worksheet Functions (UDFs) 35
PyXLL User Guide, Release 4.2.4
@xl_func
def function_with_defaults(a, b=2, c=3):
return a + b + c
@xl_func
def function_with_star_args(a,
*
args):
return a + sum(args)
It is not possible to pass named arguments from Excel, so Python functions accepting
**
kwargs cannot be used. As
an alternative, you can pass a dictionary as a single argument (see Dictionary Types).
3.3.4 Error Handling
• Exceptions raised by a UDF
• Passing Errors as Values
• Retrieving Error Information
Exceptions raised by a UDF
Whenever an Exception is raised that isn’t caught, PyXLL will write it to the log file and return an error to Excel.
PyXLL’s error handling can be customized by setting error_handler in the PYXLL section of the pyxll.cfg config
file.
[PYXLL]
error_handler = your_module.error_handler
The error handler is a function that takes the exception type, exception value and traceback of the uncaught exception,
e.g.:
def error_handler(exc_type, exc_value, exc_traceback):
"""error handler that converts an Exception to a string"""
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
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:
Excel error Python exception type
#NULL! LookupError
#DIV/0! ZeroDivisionError
#VALUE! ValueError
#REF! ReferenceError
#NAME! NameError
#NUM! ArithmeticError
#NA! RuntimeError
3.3. Worksheet Functions (UDFs) 36
PyXLL User Guide, Release 4.2.4
Passing Errors as Values
Sometimes it is useful to be able to pass a cell value from Excel to python when the cell value is actually an error, or
vice-versa.
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 come through to 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
1.#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 also a slight performance penalty for using the float_nan
type when compared to a plain float.
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 get_last_error.
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. This is because doing so
would add additional performance overhead to every function call.
3.3. Worksheet Functions (UDFs) 37
PyXLL User Guide, Release 4.2.4
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)
3.3. Worksheet Functions (UDFs) 38
PyXLL User Guide, Release 4.2.4
3.3.5 Resizing Array Formulas
When returning an array, PyXLL can automatically resize the range used by the formula. To have PyXLL do this the
auto_resize option to xl_func should be to the True. e.g:
from pyxll import xl_func
@xl_func("int rows, int cols: int[][]", auto_resize=True)
def make_array(rows, cols, value):
# create a 2d array of size (rows x cols)
array = []
for i in range(rows):
row = []
for j in range(cols):
row.append(i
*
cols + j)
array.append(row)
return array
The default setting for auto_resize can be set in the config.
See arrayfuncs for more details about array functions.
3.3.6 Documenting Functions
When a python function is exposed to Excel the docstring of that function is visible in Excel’s function wizard dialog.
Parameter documentation may also be provided help the user know how to call the function. The most convenient way
to add parameter documentation is to add it to the docstring as shown in the following example:
from pyxll import xl_func
@xl_func
def py_round(x, n):
"""
Return a number to a given precision in decimal digits.
:param x: floating point number to round
:param n: number of decimal digits
"""
return round(x, n)
Here PyXLL will automatically detect that the last two lines of the docstring are parameter documentation. They
will appear in the function wizard as help strings for the parameters when selected. The first line will be used as the
function description.
One or more of any of the characters :, - or = may be used to separate the parameter name from it’s description, and
the Sphinx style :param x: description is also recognized.
Parameter documentation may also be added by passing a dictionary of parameter names to help strings to xl_func
as the keyword argument arg_descriptions if it is not desirable to add it to the docstring for any reason.
3.3. Worksheet Functions (UDFs) 39
PyXLL User Guide, Release 4.2.4
3.3.7 Functions with a Variable Number of Arguments
In Python it is possible to declare a function that takes a variable number of arguments using the special
*
args
notation.
These functions can be exposed to Excel as worksheet functions that also take a variable number of arguments.
from pyxll import xl_func
@xl_func
def py_join(sep,
*
args):
"""Joins a number of args with a separator"""
return sep.join(map(str, args))
3.3. Worksheet Functions (UDFs) 40
PyXLL User Guide, Release 4.2.4
It is also possible to set the type of the args in the function signature. When doing that the type for all of the variable
arguments must be the same. For mixed types, use the var type.
from pyxll import xl_func
@xl_func("str sep, str
*
args: str")
def py_join(sep,
*
args):
"""Joins a number of args with a separator"""
return sep.join(args)
Unlike Python, Excel has some limits on the number of arguments that can be provided to a function. For practical
purposes the limit is high enough that it is unlikely to be a problem. The absolute limit for the number of arguments is
255, however the actual limit for a function may be very slightly lower
7
.
3.3.8 Interrupting Functions
Long running functions can cause Excel to become unresponsive and sometimes it’s desirable to allow the user to
interrupt functions before they are complete.
Excel allows the user to signal they want to interrupt any currently running functions by pressing the Esc
key. If a Python function has been registered with allow_abort=True (see xl_func) PyXLL will raise a
KeyboardInterrupt exception if the user presses Esc while a Python function is being called.
This will usually cause the function to exit, but if the KeyboardInterrupt exception is caught then it will not.
Also, as it is a Python exception that’s raised, if the Python function is calling out to something else (e.g. a C extension
library) the exception may not be registered until control is returned to Python.
Enabling allow_abort registers a Python trace function for the duration of the call to the function. This can have a
negative impact on performance and so it may not be suitable for all functions. The Python interpreter calls the trace
function very frequently, and PyXLL checks Excel’s abort status during this trace function. To reduce the performance
overhead of calling this trace function, PyXLL throttles how often it checks Excel’s abort status and this throttling can
be fine-tuned with the config settings abort_throttle_time and abort_throttle_count. See PyXLL Settings for more
details.
The allow_abort feature can be enabled for all functions by setting it in the configuration. This should be used with
caution because of the performance implications outlined above.
[PYXLL]
allow_abort = 1
It is not enabled by default because of the performance impact mentioned above, and also as it can interfere with the
operation of some remote debugging tools that use the same Python trace mechanism.
3.4 Menu Functions
• Custom Menu Items
• New Menus
• Sub-Menus
7
The technical reason this limit is lower is because when the function is registered with Excel, a string is used to tell Excel all the argument and
return types, as well as any modifiers for things like whether the function is thread safe or not. The total length of this string cannot exceed 255
characters so, even though Excel might be able to handle 255 arguments, it’s not always possible to register a function with 255 arguments because
of the limit of that string.
3.4. Menu Functions 41
PyXLL User Guide, Release 4.2.4
3.4.1 Custom Menu Items
The xl_menu decorator is used to expose a python function as a menu callback. PyXLL creates the menu item
for you, and when it’s selected your python function is called. That python function can call back into Excel using
win32com or comtypes to make changes to the current sheet or workbook.
Different menus can be created and you can also create submenus. The order in which the items appear is controlled
by optional keyword arguments to the xl_menu decorator.
Here’s a very simple example that displays a message box when the user selects the menu item:
from pyxll import xl_menu, xlcAlert
@xl_menu("Hello!")
def on_hello():
xlcAlert("Hello!")
Menu items may modify the current workbook, or in fact do anything that you can do via the Excel COM API. This
allows you to do anything in Python that you previously would have had to have done in VBA.
Below is an example that uses xl_app to get the Excel Application COM object and modify the current selection.
You will need to have win32com or comtypes installed for this.
from pyxll import xl_menu, xl_app
@xl_menu("win32com menu item")
def win32com_menu_item():
# get the Excel Application object
xl = xl_app()
# get the current selected range
selection = xl.Selection
# set some text to the selection
selection.Value = "Hello!"
3.4. Menu Functions 42
PyXLL User Guide, Release 4.2.4
3.4.2 New Menus
As well as adding menu items to the main PyXLL addin menu it’s possible to create entirely new menus.
To create a new menu, use the menu keyword argument to the xl_menu decorator.
In addition, if you want to control the order in which menus are added you may use the menu_order integer keyword
argument. The higher the value, the later in the ordering the menu will be added. The menu order my also be set in
the config (see configuration).
Below is a modification of an earlier menu example that puts the menu item in a new menu, called “New Menu”:
from pyxll import xl_menu, xlcAlert
@xl_menu("My menu item", menu="New Menu")
def my_menu_item():
xlcAlert("new menu example")
3.4.3 Sub-Menus
Sub-menus may also be created. To add an item to a sub-menu, use the sub_menu keyword argument to the xl_menu
decorator.
All sub-menu items share the same sub_menu argument. The ordering of the items within the submenu is controlled
by the sub_order integer keyword argument. In the case of sub-menus, the order keyword argument controls the order
of the sub-menu within the parent menu. The menu order my also be set in the config (see configuration).
For example, to add the sub-menu item “TEST” to the sub-menu “Sub Menu” of the main menu “My Menu”, you
would use a decorator as illustrated by the following code:
from pyxll import xl_menu, xlcAlert
@xl_menu("TEST", menu="New Menu", sub_menu="Sub Menu")
def my_submenu_item():
xlcAlert("sub menu example")
3.4. Menu Functions 43
PyXLL User Guide, Release 4.2.4
3.5 Customizing the Ribbon
• Introduction
• Creating a Custom Tab
• Action Functions
• Using Images
• Modifying the Ribbon
3.5.1 Introduction
The Excel Ribbon interface can be customized using PyXLL. This enables you to add features to Excel in Python that
are properly integrated with Excel for an intuitive user experience.
The ribbon customization is defined using an XML file, referenced in the config with the ribbon setting. This can be
set to a filename relative to the config file, or as as absolute path.
The ribbon XML file uses the standard Microsoft CustomUI schema. This is the same schema you would use if you
were customizing the ribbon using COM, VBA or VSTO and there are various online resources from Microsoft that
document it
1
.
Actions referred to in the ribbon XML file are resolved to Python functions. The full path to the function must be
included (e.g. “module.function”) and the module must be on the python path so it can be imported. Often it’s useful
1
Microsoft Ribbon Resources
• Ribbon XML
• Walkthrough: Creating a Custom Tab by Using Ribbon XML
• XML Schema Reference
3.5. Customizing the Ribbon 44
PyXLL User Guide, Release 4.2.4
to include the modules used by the ribbon in the modules list in the config so that when PyXLL is reloaded those
modules are also reloaded, but that is not strictly necessary.
3.5.2 Creating a Custom Tab
• Create a new ribbon xml file. The one below contains a single tab Custom Tab and a single button.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
• Set ribbon in the config file to the filename of the newly created ribbon XML file.
[PYXLL]
ribbon = <full path to xml file>
• Start Excel (or reload PyXLL if Excel is already started).
The tab appears in the ribbon with a single text button as specified in the XML file. Clicking on the button doesn’t do
anything yet.
3.5. Customizing the Ribbon 45
PyXLL User Guide, Release 4.2.4
3.5.3 Action Functions
Anywhere a callback method is expected in the ribbon XML you can use the name of a Python function.
Many of the controls used in the ribbon have an onAction attribute. This should be set to the name of a Python function
that will handle the action.
• To add an action handler to the example above first modify the XML file to add the onAction attribute to the text
button
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"
onAction="ribbon_functions.on_text_button"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
• Create the ribbon_functions module with the filename ribbon_functions.py and add the on_text_button function
2
.
Note that the module name isn’t important, only that it matches the one used in the xml file.
from pyxll import xl_app
def on_text_button(control):
xl = xl_app()
xl.Selection.Value = "This text was added by the Ribbon."
• Add the module to the pyxll config
3
.
[PYXLL]
modules = ribbon_functions
• Reload PyXLL. The custom tab looks the same but now clicking on the text button calls the Python function.
3.5.4 Using Images
Some controls can use an image to give the ribbon whatever look you like. These controls have an image attribute and
a getImage attribute.
The image attribute is set to the filename of an image you want to load. The getImage attribute is a function that will
return a COM object that implements the IPicture interface.
PyXLL provides a function, load_image, that loads an image from disk and returns a COM Picture object. This
can be used instead of having to do any COM programming in Python to load images.
When images are referenced by filename using the image attribute Excel will load them using a basic image handler.
This basic image handler is rather limited and doesn’t handle PNG files with transparency, so it’s recommended to use
load_image instead. The image handler can be set as the loadImage attribute on the customUI element.
The following shows the example above with a new button added and the loadImage handler set.
2
The name of the module and function is unimportant, it just has to match the onAction attribute in the XML and be on the pythonpath so it can
be imported.
3
This isn’t strictly necessary but is helpful as it means the module will be reloaded when PyXLL is reloaded.
3.5. Customizing the Ribbon 46
PyXLL User Guide, Release 4.2.4
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
loadImage="pyxll.load_image">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"
onAction="ribbon_functions.on_text_button"/>
</group>
<group id="Tools" label="Tools">
<button id="Reload"
size="large"
label="Reload PyXLL"
onAction="pyxll.reload"
image="reload.png"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
3.5.5 Modifying the Ribbon
Sometimes its convenient to be able to update the ribbon after Excel has started, without having to change the pyxll.cfg
config file.
For example, if your addin is used by multiple users with different roles then one single ribbon may not be applicable
for each user. Or, you may want to allow the user to switch between different ribbons depending on what they’re
working on.
3.5. Customizing the Ribbon 47
PyXLL User Guide, Release 4.2.4
There are some Python functions you can use from your code to update the ribbon:
• get_ribbon_xml
• set_ribbon_xml
• set_ribbon_tab
• remove_ribbon_tab
These functions can be used to completely replace the current ribbon (set_ribbon_xml) or just to add, replace or
remove tabs (set_ribbon_tab, remove_ribbon_tab).
The ribbon can be updated anywhere from Python code running in PyXLL. Typically this would be when Excel starts
up using the xl_on_open and xl_on_reload event handlers, or from an action function from the current ribbon.
3.6 Context Menu Functions
• Introduction
• Adding a Python Function to the Context Menu
• Creating Sub-Menus
• Dynamic Menus
• References
3.6.1 Introduction
Context menus are the menus that appear in Excel when your right-click on something, most usually a cell in the
current workbook.
These context menus have become a standard way for users to interact with their spreadsheets and are an efficient way
to get to often used functions.
With PyXLL you can add your own Python functions to the context menus.
The context menu customizations are defined using the same XML file used when customizing the Excel ribbon (see
Customizing the Ribbon). The XML file is referenced in the config with the ribbon setting. This can be set to a
filename relative to the config file, or as an absolute path.
The ribbon XML file uses the standard Microsoft CustomUI schema. This is the same schema you would use if you
were customizing the ribbon using COM, VBA or VSTO and there are various online resources from Microsoft that
document it
1
. For adding context menus, you must use the 2009 version of the schema or later.
Actions referred to in the ribbon XML file are resolved to Python functions. The full path to the function must be
included (e.g. “module.function”) and the module must be on the python path so it can be imported. Often it’s useful
to include the modules used by the ribbon in the modules list in the config so that when PyXLL is reloaded those
modules are also reloaded, but that is not strictly necessary.
3.6.2 Adding a Python Function to the Context Menu
• Create a new ribbon xml file, or add the contextMenus section from below to your existing ribbon xml file.
1
XML Schema Reference
3.6. Context Menu Functions 48
PyXLL User Guide, Release 4.2.4
Note that you must use the 2009 version of the schema in the customUI element, and the contextMenus
element must be placed after the ribbon element.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<button id="MyButton" label="Toggle Case Upper/Lower/Proper"
insertBeforeMso="Cut"
onAction="context_menus.toggle_case"
imageMso="HappyFace"/>
</contextMenu>
</contextMenus>
</customUI>
In the xml above, insertBeforeMso is used to insert the menu item before the existing “Cut” menu item. This
may be removed if you want the item placed at the end of the menu. Also, imageMso may be replaced with image
and set to the path of an image file rather than using one of Excel’s built in bitmaps (see load_image).
• If you’ve not already done so, set ribbon in the config file to the filename of the ribbon XML file.
[PYXLL]
ribbon = <full path to xml file>
• Create the context_menus module with the filename context_menus.py and add the toggle_case function. Note
that the module name isn’t important, only that it matches the one referenced in the onAction handler in the
xml file above.
from pyxll import xl_app
def toggle_case(control):
"""Toggle the case of the currently selected cells"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
# toggle between upper, lower and proper case
if value.isupper():
value = value.lower()
elif value.islower():
value = value.title()
else:
value = value.upper()
# set the modified value on the cell
cell.Value = value
3.6. Context Menu Functions 49
PyXLL User Guide, Release 4.2.4
• Add the module to the pyxll config
2
.
[PYXLL]
modules = context_menus
• Start Excel (or reload PyXLL if Excel is already started).
If everything has worked, you will now see the “Toggle Case” item in the context menu when you right click on
a cell.
3.6.3 Creating Sub-Menus
Sub-menus can be added to the context menu using the menu tag.
The following adds a sub-menu after the “Toggle Case” button added above.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<button id="MyButton" label="Toggle Case Upper/Lower/Proper"
insertBeforeMso="Cut"
onAction="context_menus.toggle_case"
imageMso="HappyFace"/>
<menu id="MySubMenu" label="Case Menu" insertBeforeMso="Cut" >
<button id="Menu1Button1" label="Upper Case"
imageMso="U"
onAction="context_menus.toupper"/>
<button id="Menu1Button2" label="Lower Case"
imageMso="L"
onAction="context_menus.tolower"/>
<button id="Menu1Button3" label="Proper Case"
imageMso="P"
onAction="context_menus.toproper"/>
</menu>
</contextMenu>
</contextMenus>
</customUI>
The additional buttons use the following code, which you can copy to your context_menus.py module.:
def tolower(control):
"""Set the currently selected cells to lower case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
2
This isn’t strictly necessary but is helpful as it means the module will be reloaded when PyXLL is reloaded.
3.6. Context Menu Functions 50
PyXLL User Guide, Release 4.2.4
continue
cell.Value = value.lower()
def toupper(control):
"""Set the currently selected cells to upper case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.upper()
def toproper(control):
"""Set the currently selected cells to 'proper' case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.title()
3.6.4 Dynamic Menus
As well as statically declaring menus as above, you can also generate menus on the fly in your Python code.
A dynamic menu calls a Python function to get a xml fragment that tells Excel how to display the menu. This can be
useful when the items you want to appear in a menu might change.
The following shows how to declare a dynamic menu.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<dynamicMenu id="MyDynamicMenu"
label= "My Dynamic Menu"
imageMso="ChangeCase"
3.6. Context Menu Functions 51
PyXLL User Guide, Release 4.2.4
insertBeforeMso="Cut"
getContent="context_menus.dynamic_menu"/>
</contextMenu>
</contextMenus>
</customUI>
The getContent callback references the dynamic_menu function in the context_menus module.:
def dynamic_menu(control):
"""Return an xml fragment for the dynamic menu"""
xml = """
<menu xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<button id="Menu2Button1" label="Upper Case"
imageMso="U"
onAction="context_menus.toupper"/>
<button id="Menu2Button2" label="Lower Case"
imageMso="L"
onAction="context_menus.tolower"/>
<button id="Menu2Button3" label="Proper Case"
imageMso="P"
onAction="context_menus.toproper"/>
</menu>
"""
return xml
3.6.5 References
• XML Schema Reference
• https://msdn.microsoft.com/en-us/library/dd926324(v=office.12).aspx
• http://interoperability.blob.core.windows.net/files/MS-CUSTOMUI2/{[}MS-CUSTOMUI2{]}-150904.pdf
3.7 Macro Functions
• Introduction
• Exposing Functions as Macros
• Keyboard Shortcuts
• Calling Macros From Excel
3.7.1 Introduction
You can write an Excel macro in python to do whatever you would previously have used VBA for. Macros work in a
very similar way to worksheet functions. To register a function as a macro you use the xl_macro decorator.
Macros are useful as they can be called when GUI elements (buttons, checkboxes etc.) fire events. They can also be
called from VBA.
3.7. Macro Functions 52
PyXLL User Guide, Release 4.2.4
Macro functions can call back into Excel using the Excel COM API (which is identical to the VBA Excel object
model). The function xl_app can be used to get the Excel.Application COM object (using either win32com or
comtypes), which is the COM object corresponding to the Application object in VBA.
See also Python as a VBA Replacement.
3.7.2 Exposing Functions as Macros
Python functions to be exposed as macros are decorated with the xl_macro decorator imported from the pyxll
module.
from pyxll import xl_macro, xl_app, xlcAlert
@xl_macro
def popup_messagebox():
xlcAlert("Hello")
@xl_macro
def set_current_cell(value):
xl = xl_app()
xl.Selection.Value = value
@xl_macro("string n: int")
def py_strlen(n):
return len(x)
3.7.3 Keyboard Shortcuts
You can assign keyboard shortcuts to your macros by using the ‘shortcut’ keyword argument to the xl_macro
decorator, or by setting it in the SHORTCUTS section in the config.
Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For
example, ‘Ctrl+Shift+R’.
from pyxll import xl_macro, xl_app
@xl_macro(shortcut="Alt+F3")
def macro_with_shortcut():
xlcAlert("Alt+F3 pressed")
If a key combination is already in use by Excel it may not be possible to assign a macro to that combination.
In addition to letter, number and function keys, the following special keys may also be used (these are not case sensitive
and cannot be used without a modifier key):
• Backspace
• Break
• CapsLock
• Clear
• Delete
• Down
• End
3.7. Macro Functions 53
PyXLL User Guide, Release 4.2.4
• Enter
• Escape
• Home
• Insert
• Left
• NumLock
• PgDn
• PgUp
• Right
• ScrollLock
• Tab
3.7.4 Calling Macros From Excel
Macros defined with PyXLL can be called from Excel the same way as any other Excel macros.
The most usual way is to assign a macro to a control. To do that, first add the Forms toolbox by going to the Tools
Customize menu in Excel and check the Forms checkbox. This will present you with a panel of different controls
which you can add to your worksheet. For the message box example above, add a button and then right click and
select ‘Assign macro. . . ’. Enter the name of your macro, in this case popup_messagebox. Now when you click that
button the macro will be called.
3.7. Macro Functions 54
PyXLL User Guide, Release 4.2.4
It is also possible to call your macros from VBA. While PyXLL may be used to reduce the need for VBA in your
projects, sometimes it is helpful to be able to call python functions from VBA.
For the py_strlen example above, to call that from VBA you would use the Run VBA function, e.g.
Sub SomeVBASubroutine
x = Run("py_strlen", "my string")
End Sub
3.8 Real Time Data (RTD)
• Introduction
• Streaming Data From Python
• Example Usage
• RTD Data Types
• Using the asyncio Event Loop
• Throttle Interval
3.8. Real Time Data (RTD) 55
PyXLL User Guide, Release 4.2.4
3.8.1 Introduction
Real Time Data (or RTD) is data that updates according to it’s own schedule, not just when it is re-evaluated (as is the
case for a regular Excel worksheet function).
Examples of real time data include stock prices and other live market data, server loads or the progress of an external
task.
Real Time Data has been a first-class feature of Excel since Excel 2002. It uses a hybrid push-pull mechanism where
the source of the real time data notifies Excel that new data is available, and then some small time later Excel queries
the real time data source for it’s current value and updates the value displayed.
3.8.2 Streaming Data From Python
PyXLL provides a convenient and simple way to stream real time data to Excel without the complexity of writing (and
registering) a Real Time Data COM server.
Real Time Data functions are registered in the same way as other worksheet functions using the xl_func decorator.
Instead of returning a single fixed value, however, they return an instance of an class derived from RTD.
RTD functions have the return type rtd.
When a function returns a RTD instance PyXLL sets up the real time data subscription in Excel and each time the
value property of the RTD instance is set Excel is notified that new data is ready.
If multiple function calls from different cells return the same instance of an RTD class then those cells are subscribed
to the same real time data source, so they will all update whenever the value property is set.
3.8.3 Example Usage
The following example shows a class derived from RTD that periodically updates its value to the current time.
3.8. Real Time Data (RTD) 56
PyXLL User Guide, Release 4.2.4
It uses a separate thread to set the value property, which notifies Excel that new data is ready.
from pyxll import xl_func, RTD
from datetime import datetime
import threading
import logging
import time
_log = logging.getLogger(__name__)
class CurrentTimeRTD(RTD):
"""CurrentTimeRTD periodically updates its value with the current
date and time. Whenever the value is updated Excel is notified and
when Excel refreshes the new value will be displayed.
"""
def __init__(self, format):
initial_value = datetime.now().strftime(format)
super(CurrentTimeRTD, self).__init__(value=initial_value)
self.__format = format
self.__running = True
self.__thread = threading.Thread(target=self.__thread_func)
self.__thread.start()
def connect(self):
# Called when Excel connects to this RTD instance, which occurs
# shortly after an Excel function has returned an RTD object.
_log.info("CurrentTimeRTD Connected")
def disconnect(self):
3.8. Real Time Data (RTD) 57
PyXLL User Guide, Release 4.2.4
# Called when Excel no longer needs the RTD instance. This is
# usually because there are no longer any cells that need it
# or because Excel is shutting down.
self.__running = False
_log.info("CurrentTimeRTD Disconnected")
def __thread_func(self):
while self.__running:
# Setting 'value' on an RTD instance triggers an update in Excel
new_value = datetime.now().strftime(self.__format)
if self.value != new_value:
self.value = new_value
time.sleep(0.5)
In order to access this real time data in Excel all that’s required is a worksheet function that returns an instance of this
CurrentTimeRTD class.
@xl_func("string format: rtd")
def rtd_current_time(format="%Y-%m-%d %H:%M:%S"):
"""Return the current time as 'real time data' that
updates automatically.
:param format: datetime format string
"""
return CurrentTimeRTD(format)
Note that the return type of this function is rtd.
When this function is called from Excel the value displayed will periodically update, even though the function
rtd_current_time isn’t volatile and only gets called once.
=rtd_current_time()
3.8.4 RTD Data Types
RTD functions can return all the same data types as normal Worksheet Functions, including array types and cached
Python objects.
By default, the rtd return type will use the same logic as a worksheet function with no return type specified or the
var type.
To specify the return type explicity you have to include it in the function signature as a parameter to the rtd type.
For example, the following is how an RTD function that returns Python objects via the internal object cache would be
declared:
@xl_func("string x: rtd<object>")
def rtd_object_func(x):
# MyRTD sets self.value to a non-trivial Python object
return MyRTD(x)
Although RTD functions can return array types, they cannot be automatically resized and so the array formula needs
to be entered manually using Ctrl+Shift+Enter (see Array Types).
3.8. Real Time Data (RTD) 58
PyXLL User Guide, Release 4.2.4
3.8.5 Using the asyncio Event Loop
Instead of managing your own background threads and thread pools when writing RTD functions, you can use PyXLL’s
asyncio event loop instead (new in PyXLL 4.2 and requires Python 3.5.1 or higher).
This can be useful if you have RTD functions that are waiting on IO a lot of the time. If you can take advantage of
Python’s async and await keywords so as not to block the event loop then making your RTD function run on the
asyncio event loop can make certain things much simpler.
The methods RTD.connect and RTD.disconnect can both be async methods. If they are then PyXLL will
schedule them automatically on it’s asyncio event loop.
The example below shows how using the event loop can eliminate the need for your own thread management.
See The asyncio Event Loop for more details.
from pyxll import RTD, xl_func
import asyncio
class AsyncRTDExample(RTD):
def __init__(self):
super().__init__(value=0)
self.__stopped = False
async def connect(self):
while not self.__stopped:
# Yield to the event loop for 1s
await asyncio.sleep(1)
# Update value (which notifies Excel)
self.value += 1
async def disconnect(self):
self.__stopped = True
@xl_func(": rtd<int>")
def async_rtd_example():
return AsyncRTDExample()
3.8.6 Throttle Interval
Excel throttles the rate of updates made via RTD functions. Instead of updating every time it is notified of new data it
waits for a period of time and then updates all cells with new data at once.
The default throttle time is 2,000 milliseconds (2 seconds). This means that even if you are setting value on an RTD
instance more frequently you will not see the value in Excel updating more often than once every two seconds.
The throttle interval can be changed by setting Application.RTD.ThrottleInterval (in milliseconds). Setting the throttle
interval is persistent across Excel sessions (meaning that if you close and restart Excel then the value you set the
interval to will be remembered).
The following code shows how to set the throttle interval in Python.
from pyxll import xl_func, xl_app
@xl_func("int interval: string")
def set_throttle_interval(interval):
3.8. Real Time Data (RTD) 59
PyXLL User Guide, Release 4.2.4
xl = xl_app()
xl.RTD.ThrottleInterval = interval
return "OK"
Alternatively it can be set in the registry by modifying the following key. It is a DWORD in milliseconds.
HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options\RTDThrottleInterval
3.9 Asynchronous Functions
• Asynchronous Worksheet Functions
• The asyncio Event Loop
• Real Time Data and asyncio
• Before Python 3.5
Excel has supported asynchronous worksheet functions since Office 2010. To be able to use asynchronous worksheet
functions with PyXLL you will need to be using at least that version of Office.
Excel asynchronous worksheet functions are called as part of Excel’s calculation in the same way as other functions,
but rather than return a result, they can schedule some work and return immediately, allowing Excel’s calculation to
progress while the scheduled work for the asynchronous function continues concurrently. When the asynchronous
work has completed, Excel is notified.
Asynchronous functions still must be completed as part of Excel’s normal calculation phase. Using asynchronous
functions means that many more functions can be run concurrently, but Excel will still show as calculating until all
asynchronous functions have returned.
Functions that use IO, such as requesting results from a database or web server, are well suited to being made into
asynchronous functions. For CPU intensive tasks
1
using the thread_safe option to xl_func may be a better alterna-
tive.
If your requirement is to return the result of a very long running function back to Excel after recalculating has com-
pleted, you may want to consider using an RTD (Real Time Data (RTD)) function instead. An RTD function doesn’t
have to keep updating Excel, it can just notify Excel once when a single calculation is complete. Also, it can be used
to notify the user of progress which for very long running tasks can be helpful.
1
For CPU intensive problems that can be solved using multiple threads (i.e. the CPU intensive part is done without the Python Global Interpreter
Lock, or GIL, being held) use the thread_safe argument to xl_func to have Excel automatically schedule your functions using a thread pool.
3.9. Asynchronous Functions 60
PyXLL User Guide, Release 4.2.4
3.9.1 Asynchronous Worksheet Functions
Python 3.5 Required
Using the async keyword requires a minimum of Python 3.5.1 and PyXLL 4.2. If you do not have these minimum
requirements see Before Python 3.5.
If you are using a modern version of Python, version 3.5.1 or higher, writing asynchronous Excel worksheet functions
is as simple as adding the async keyword to your function definition. For earlier versions of Python, or for PyXLL
versions before 4.2, or if you just don’t want to use coroutines, see Before Python 3.5.
The following example shows how the asynchronous http package aiohttp can be used with PyXLL to fetch stock
prices without blocking the Excel’s calculation while it waits for a response
2
from pyxll import xl_func
import aiohttp
import json
endpoint = "https://api.iextrading.com/1.0/"
@xl_func
async def iex_fetch(symbol, key):
"""returns a value for a symbol from iextrading.com"""
url = endpoint + f"stock/{symbol}/batch?types=quote"
2
Asynchronous functions are only available in Excel 2010. Attempting to use them in an earlier version will result in an error.
3.9. Asynchronous Functions 61
PyXLL User Guide, Release 4.2.4
async with aiohttp.ClientSession() as session:
async with session.get(url) as response:
assert response.status == 200
data = await response.read()
data = json.loads(data)["quote"]
return data.get(key, "#NoData")
The function above is marked async. In Python, as async function like this is called a coroutine. When the coroutine
decorated with xl_func is called from Excel, PyXLL schedules it to run on an asyncio event loop.
The coroutine uses await when calling response.read() which causes it to yield to the asyncio event loop
while waiting for results from the server. This allows other coroutines to continue rather than blocking the event loop.
Note that if you do not yield to the event loop while waiting for IO or another request to complete, you will be blocking
the event loop and so preventing other coroutines from running.
If you are not already familiar with how the async and await keywords work in Python, we recommend you read
the following sections of the Python documentation:
• Coroutines and Tasks
• asyncio — Asynchronous I/O
3.9.2 The asyncio Event Loop
Using the asyncio event loop with PyXLL requires a minimum of Python 3.5.1 and PyXLL 4.2. If you do not have
these minimum requirements see Before Python 3.5.
When a coroutine (async function) is called from Excel, it is scheduled on the asyncio event loop. PyXLL starts this
event loop on demand, the first time an asynchronous function is called.
For most cases, PyXLL default asyncio event loop is well suited. However the event loop that PyXLL uses can be
replaced by setting start_event_loop and stop_event_loop in the PYXLL section of the pyxll.cfg file. See
PyXLL Settings for more details.
To schedule tasks on the event loop outside of an asynchronous function, the utility function get_event_loop can
be used. This will create and start the event loop, if it’s not already started, and return it.
By default, the event loop runs on a single background thread. To schedule a function it is therefore recommended to
use loop.call_soon_threadsafe, or loop.create_task to schedule a coroutine.
3.9.3 Real Time Data and asyncio
RTD (Real Time Data (RTD)) functions are often used for streaming in data from external sources to Excel, and these
types of applications can work well with asyncio.
An RTD function returns an instance of the RTD type, which is used to notify Excel as new data becomes available.
When Excel is ready to start receiving data from the RTD instance it calls RTD.connect and RTD.disconnect.
Both the RTD.connect and RTD.disconnect methods can be async methods (coroutines). If they are corou-
tines they will be scheduled on PyXLL’s asyncio event loop.
The following example shows how an RTD function can be written using coroutines. This avoids the complexity of
having to launch a background thread or manage a thread pool.
3.9. Asynchronous Functions 62
PyXLL User Guide, Release 4.2.4
from pyxll import RTD, xl_func
import asyncio
class AsyncRTDExample(RTD):
def __init__(self):
super().__init__(value=0)
self.__stopped = False
async def connect(self):
while not self.__stopped:
# Yield to the event loop for 1s
await asyncio.sleep(1)
# Update value (which notifies Excel)
self.value += 1
async def disconnect(self):
self.__stopped = True
@xl_func(": rtd<int>")
def async_rtd_example():
return AsyncRTDExample()
3.9.4 Before Python 3.5
Or with Python >= 3.5. . .
Everything in this section still works with Python 3.5 onwards.
If you are using an older version of Python than 3.5.1, of if you have not yet upgraded to PyXLL 4.2 or later, you can
still use asynchronous worksheet functions but you will not be able to use the async keyword to do so.
Asynchronous worksheet functions are declared in the same way as regular worksheet functions by using the
xl_func decorator, but with one difference. To be recognised as an asynchronous worksheet function, one of the
function argument must be of the type async_handle.
The async_handle parameter will be a unique handle for that function call, represented by the class
XLAsyncHandle and it must be used to return the result when it’s ready. A value must be returned to Excel using
xlAsyncReturn or (new in PyXLL 4.2) the methods XLAsyncHandle.set_value and XLAsyncHandle.
set_error. Asynchronous functions themselves should not return a value.
The XLAsyncHandle instance is only valid during the worksheet recalculation cycle in which that the function was
called. If the worksheet calculation is cancelled or interrupted then calling xlAsyncReturn with an expired handle
will fail. For example, when a worksheet calculated (by pressing F9, or in response to a cell being updated if automatic
calculation is enabled) and some asynchronous calculations are invoked, if the user interrupts the calculation before
those asynchronous calculations complete then calling xlAsyncReturn after the worksheet calculation has stopped
will result in a exception being raised.
For long running calculations that need to pass results back to Excel after the sheet recalculation is complete you
should use a Real Time Data (RTD) function.
Here’s an example of an asynchronous function
2
3.9. Asynchronous Functions 63
PyXLL User Guide, Release 4.2.4
from pyxll import xl_func, xlAsyncReturn
from threading import Thread
import time
import sys
class MyThread(Thread):
def __init__(self, async_handle, x):
Thread.__init__(self)
self.__async_handle = async_handle
self.__x = x
def run(self):
try:
# here would be your call to a remote server or something like that
time.sleep(5)
xlAsyncReturn(self.__async_handle, self.__x)
except:
self.__async_handle.set_error(
*
sys.exc_info()) # New in PyXLL 4.2
# no return type required as Excel async functions don't return a value
# the excel function will just take x, the async_handle is added automatically by
˓→Excel
@xl_func("async_handle<int> h, int x")
def my_async_function(h, x):
# start the request in another thread (note that starting hundreds of threads isn
˓→'t advisable
# and for more complex cases you may wish to use a thread pool or another
˓→strategy)
thread = MyThread(h, x)
thread.start()
# return immediately, the real result will be returned by the thread function
return
The type parameter to async_handle (e.g. async_handle<date>) is optional. When provided, it is used to
convert the value returned via xlAsyncReturn to an Excel value. If omitted, the var type is used.
3.10 Python as a VBA Replacement
• The Excel Object Model
• Accessing the Excel Object Model in Python
• Differences between VBA and Python
– Case Sensitivity
– Calling Methods
– Implicit Objects and ‘With’
– Indexing Collections
• Enums and Constant Values
3.10. Python as a VBA Replacement 64
PyXLL User Guide, Release 4.2.4
• Notes on Debugging
Everything you can write in VBA can be done in Python. This page contains information that will help you translate
your VBA code into Python.
Please note that the Excel Object Model is part of Excel and documented by Microsoft. The classes and methods
from that API used in this documentation are not part of PyXLL, and so please refer to the Excel Object Model
documentation for more details about their use.
See also Macro Functions.
3.10.1 The Excel Object Model
When programming in VBA you interact with the Excel Object Model. For example, when writing
Sub Macro1()
Range("B11:K11").Select
EndSub
what you are doing is constructing a Range object and calling the Select method on it. The Range object is part of the
Excel Object Model.
Most of what people talk about in reference to VBA in Excel is actually the Excel Object Model, rather than the VBA
language itself. Once you know how to interact with the Excel Object Model from Python then replacing your VBA
code with Python code will become straightforward!
The Excel Object Model is well documented by Microsoft as part of the Office VBA Reference.
The first hurdle people often face when starting the write Excel macros in Python is finding documentation for the
Excel Python classes. Once you realise that the Object Model is the same across Python and VBA you will see that
the classes documented in the Office VBA Reference are the exact same classes that you use from Python, and so you
can use the same documentation even though the example code may be written in VBA.
3.10.2 Accessing the Excel Object Model in Python
The Excel Object Model is made available to all languages using COM. Python has a couple of packages that make
calling COM interfaces very easy. If you know nothing about COM then there’s no need to worry as you don’t need
to in order to call the Excel COM API from Python.
The top-level object in the Excel Object Model is the Application object. This represents the Excel application, and
all other objects are accessed via this object.
PyXLL provides a helper function, xl_app, for retrieving the Excel Application object. By default, it uses the Python
package win32com, which is part of the pywin32 package
1
.
If you don’t already have the pywin32 package installed you can do so using pip:
pip install pypiwin32
Or if you are using Anaconda you can use conda:
conda install pywin32
1
If you prefer to use comtypes instead of win32com you can still use xl_app by passing com_package='comtypes'.
3.10. Python as a VBA Replacement 65
PyXLL User Guide, Release 4.2.4
You can use xl_app to access the Excel Application object from an Excel macro. The following example shows how
to re-write the Macro1 VBA code sample from the section above.
Note that in VBA there is an implicit object, which related to where the VBA Sub (macro) was written. Commonly,
VBA code is written directly on a sheet. In the Macro1 example above, the Range is actually a method on the sheet
that macro was written on. In Python, we will get the current active sheet instead.
from pyxll import xl_macro, xl_app
@xl_macro
def macro1():
xl = xl_app()
# 'xl' is an instance of the Excel.Application object
# Get the current ActiveSheet (same as in VBA)
sheet = xl.ActiveSheet
# Call the 'Range' method on the Sheet
xl_range = sheet.Range('B11:K11')
# Call the 'Select' method on the Range.
# Note the parentheses which are not required in VBA but are in Python.
xl_range.Select()
You can call into Excel using the Excel Object Model from macros and menu functions, and a sub-set may be called
from worksheet functions.
The PyXLL function async_call can be used in other situations to schedule Python function to be called in a way
that the Excel Object Model can be used. For example, it’s not possible to update worksheet values from a worksheet
function, but it is possible to schedule a call using async_call to update the worksheet after Excel has finished
calculating.
For testing, it can also be helpful to call into Excel from a Python prompt (or a Jupyter notebook). This can also be
done using xl_app, and in that case the first open Excel instance found will be returned.
The equivalent can be done without using xl_app using win32com directly. This is not advised when calling your
Python code from Excel however, as it may return an Excel instance other than the one you expect.
from win32com.client.gencache import EnsureDispatch
# Get the first open Excel.Application found, or launch a new one
xl = EnsureDispatch('Excel.Application')
3.10.3 Differences between VBA and Python
Case Sensitivity
Python is case sensitive. This means that code fragments like r.Value and r.value are different (note the capital
V in the first case. In VBA they would be treated the same, but in Python you have to pay attention to the case you use
in your code.
If something is not working as expected, check the PyXLL log file. Any uncaught exceptions will be logged there, and
if you have attempted to access a property using the wrong case then you will probably see an AttributeError
exception.
3.10. Python as a VBA Replacement 66
PyXLL User Guide, Release 4.2.4
Calling Methods
In Python, parentheses (()) are always used when calling a method. In VBA, they may be omitted.
Neglecting to add parentheses in Python will result in the method not being called, so it’s important to be aware of
which class attributes are methods and which are properties.
For example, the method Select on the Range type is a method and so must be called with parentheses in Python,
but in VBA they are omitted.
' Select is a method and is called without parentheses in VBA
Range("B11:K11").Select
from pyxll import xl_app
xl = xl_app()
# In Python, the parentheses are necessary to call the method
xl.Range('B11:K11').Select()
Keyword arguments may be passed in both VBA and Python, but in Python keyword arguments use = instead of the
:= used in VBA.
Accessing properties does not require parentheses, and doing so will give unexpected results! For example, the
range.Value property will return the value of the range. Adding () to it will attempt to call that value, and
as the value will not be callable it will result in an error.
from pyxll import xl_app
xl = xl_app()
# Value is a property and so no parentheses are used
value = xl.Range('B11:K11').Value
Implicit Objects and ‘With’
When writing VBA code, the code is usually written ‘on’ an object like a WorkBook or a Sheet. That object is used
implicitly when writing VBA code.
If using a ‘With..End’ statement in VBA, the target of the ‘With’ statement becomes the implicit object.
If a property is not found on the current implicit object (e.g. the one specified in a ‘With..End’ statement) then the next
one is tried (e.g. the Worksheet the Sub routine is associated with). Finally, the Excel Application object is implicitly
used.
In Python there is no implicit object and the object you want to reference must be specified explicitly.
For example, the following VBA code selects a range and alters the column width.
Sub Macro2()
' ActiveSheet is a property of the Application
Set ws = ActiveSheet
With ws
' Range is a method of the Sheet
Set r = Range("A1:B10")
' Call Select on the Range
r.Select
End With
3.10. Python as a VBA Replacement 67
PyXLL User Guide, Release 4.2.4
' Selection is a property of the Application
Selection.ColumnWidth = 4
End Sub
To write the same code in Python each object has to be referenced explicitly.
from pyxll import xl_macro, xl_app
@xl_macro
def macro2():
# Get the Excel.Application instance
xl = xl_app()
# Get the active sheet
ws = xl.ActiveSheet
# Get the range from the sheet
r = ws.Range('A1:B10')
# Call Select on the Range
r.Select()
# Change the ColumnWidth property on the selection
xl.Selection.ColumnWidth = 4
Indexing Collections
VBA uses parentheses (()) for calling methods and for indexing into collections.
In Python, square braces ([]) are used for indexing into collections.
Care should be taken when indexing into Excel collections, as Excel uses an index offset of 1 whereas Python uses
0. This means that to get the first item in a normal Python collection you would use index 0, but when accessing
collections from the Excel Object Model you would use 1.
3.10.4 Enums and Constant Values
When writing VBA enum values are directly accessible in the global scope. For example, you can write
Set cell = Range("A1")
Set cell2 = cell.End(Direction:=xlDown)
In Python, these enum values are available as constants in the win32com.client.constants package. The
code above would be re-written in Python as follows
from pyxll import xl_app
from win32com.client import constants
xl = xl_app()
cell = xl.Range('A1')
cell2 = cell.End(Direction=constants.xlDown)
3.10. Python as a VBA Replacement 68
PyXLL User Guide, Release 4.2.4
3.10.5 Notes on Debugging
The Excel VBA editor has integrating debugging so you can step through the code and see what’s happening at each
stage.
When writing Python code it is sometimes easier to write the code outside of Excel in your Python IDE before adapting
it to be called from Excel as a macro or menu function etc.
When calling your code from Excel, remember that any uncaught exceptions will be printed to the PyXLL log file and
so that should always be the first place you look to find what’s going wrong.
If you find that you need to be able to step through your Python code as it is being executed in Excel you will need a
Python IDE that supports remote debugging. Remote debugging is how debuggers connect to an external process that
they didn’t start themselves.
You can find instructions for debugging Python code running in Excel in this blog post Debugging Your Python Excel
Add-In.
3.10. Python as a VBA Replacement 69
CHAPTER 4
API Reference
4.1 Function Decorators
These decorators are used to expose Python functions to Excel as worksheet functions, menu functions and macros.
• xl_func
• xl_menu
• xl_macro
• xl_arg_type
• xl_return_type
• xl_arg
• xl_return
4.1.1 xl_func
xl_func(signature=None, category=PyXLL, help_topic="", thread_safe=False, macro=False,
allow_abort=None, volatile=False, disable_function_wizard_calc=False, dis-
able_replace_calc=False, name=None, auto_resize=False, hidden=False)
xl_func is decorator used to expose python functions to Excel. Functions exposed in this way can be called from
formulas in an Excel worksheet and appear in the Excel function wizard.
Parameters
• signature (string) – string specifying the argument types and, optionally, their names
and the return type. If the return type isn’t specified the var type is assumed. eg:
"int x, string y: double" for a function that takes two arguments, x and y and
returns a double.
70
PyXLL User Guide, Release 4.2.4
"float x" or "float x: var" for a function that takes a float x and returns a vari-
ant type.
If no signature is provided the argument and return types will be inferred from any type
annotations, and if there are no type annotations then the types will be assumed to be var.
See Basic Types for the built-in types that can be used in the signature.
• category (string) – String that sets the category in the Excel function wizard the ex-
posed function will appear under.
• help_topic (string) – Path of the help file (.chm) that will be available from the
function wizard in Excel.
• thread_safe (boolean) – Indicates whether the function is thread-safe or not. If True
the function may be called from multiple threads in Excel 2007 or later
• macro (boolean) – If True the function will be registered as a macro sheet equivalent
function. Macro sheet equivalent functions are less restricted in what they can do, and in
particular they can call Excel macro sheet functions such as xlfCaller.
• allow_abort (boolean) – If True the function may be cancelled by the user pressing
Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the
behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
Enabling this option has performance implications. See Interrupting Functions for more
details.
• volatile (boolean) – if True the function will be registered as a volatile function,
which means it will be called every time Excel recalculates regardless of whether any of the
parameters to the function have changed or not
• disable_function_wizard_calc (boolean) – Don’t call from the Excel function
wizard. This is useful for functions that take a long time to complete that would otherwise
make the function wizard unresponsive
• disable_replace_calc (boolean) – Set to True to stop the function being called
from Excel’s find and replace dialog.
• arg_descriptions – dict of parameter names to help strings.
• name (string) – The Excel function name. If None, the Python function name is used.
• auto_resize (boolean) – When returining an array, PyXLL can automatically resize
the range used by the formula to match the size of the result.
• hidden (boolean) – If True the UDF is hidden and will not appear in the Excel Function
Wizard.
@Since PyXLL 3.5.0
• transpose (boolean) – If true, if an array is returned it will be transposed before being
returned to Excel. This can be used for returning 1d lists as rows.
@Since PyXLL 4.2.0
Example usage:
from pyxll import xl_func
@xl_func
def hello(name):
"""return a familiar greeting"""
return "Hello, %s" % name
4.1. Function Decorators 71
PyXLL User Guide, Release 4.2.4
# Python 3 using type annotations
@xl_func
def hello2(name: str) -> str:
"""return a familiar greeting"""
return "Hello, %s" % name
# Or a signature may be provided as string
@xl_func("int n: int", category="Math", thread_safe=True)
def fibonacci(n):
"""naive iterative implementation of fibonacci"""
a, b = 0, 1
for i in xrange(n):
a, b = b, a + b
return a
See Worksheet Functions (UDFs) for more details about using the xl_func decorator, and ../userguide/arrayfuncs
for more details about array functions.
4.1.2 xl_menu
xl_menu(name, menu=None, sub_menu=None, order=0, menu_order=0, allow_abort=None, short-
cut=None)
xl_menu is a decorator for creating menu items that call Python functions. Menus appear in the ‘Addins’ section
of the Excel ribbon from Excel 2007 onwards, or as a new menu in the main menu bar in earlier Excel versions.
Parameters
• name (string) – name of the menu item that the user will see in the menu
• menu (string) – name of the menu that the item will be added to. If a menu of that name
doesn’t already exist it will be created. By default the PyXLL menu is used
• sub_menu (string) – name of the submenu that this item belongs to. If a submenu of
that name doesn’t exist it will be created
• order (int) – influences where the item appears in the menu. The higher the number, the
further down the list. Items with the same sort order are ordered lexographically. If the item
is a sub-menu item, this order influences where the sub-menu will appear in the main menu.
The menu order my also be set in the config (see configuration).
• sub_order (int) – similar to order but it is used to set the order of items within a sub-
menu
• menu_order (int) – used when there are multiple menus and controls the order in which
the menus are added
• allow_abort (boolean) – If True the function may be cancelled by the user pressing
Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the
behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
• shortcut (string) – Assigns a keyboard shortcut to the menu item. Shortcuts should be
one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol.
For example, ‘Ctrl+Shift+R’.
If the same key combination is already in use by Excel it may not be possible to assign a
menu item to that combination.
Example usage:
4.1. Function Decorators 72
PyXLL User Guide, Release 4.2.4
from pyxll import xl_menu, xlcAlert
@xl_menu("My menu item")
def my_menu_item():
xlcAlert("Menu button example")
See Menu Functions for more details about using the xl_menu decorator.
4.1.3 xl_macro
xl_macro(signature=None, allow_abort=None, name=None, shortcut=None)
xl_macro is a decorator for exposing python functions to Excel as macros. Macros can be triggered from
controls, from VBA or using COM.
Parameters
• signature (str) – An optional string that specifies the argument types and, optionally,
their names and the return type.
The format of the signature is identical to the one used by xl_func.
If no signature is provided the argument and return types will be inferred from any type
annotations, and if there are no type annotations then the types will be assumed to be var.
• allow_abort (bool) – If True the function may be cancelled by the user pressing Esc.
A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the behavior
is determined by the allow_abort setting in the config (see PyXLL Settings).
• name (string) – The Excel macro name. If None, the Python function name is used.
• shortcut (string) – Assigns a keyboard shortcut to the macro. Shortcuts should be
one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol.
For example, ‘Ctrl+Shift+R’.
If the same key combination is already in use by Excel it may not be possible to assign a
macro to that combination.
Macros can also have keyboard shortcuts assigned in the config file (see configuration).
• transpose (boolean) – If true, if an array is returned it will be transposed before being
returned to Excel.
Example usage:
from pyxll import xl_macro, xlcAlert
@xl_macro
def popup_messagebox():
"""pops up a message box"""
xlcAlert("Hello")
@xl_macro
def py_strlen(s):
"""returns the length of s"""
return len(s)
See Macro Functions for more details about using the xl_macro decorator.
4.1. Function Decorators 73
PyXLL User Guide, Release 4.2.4
4.1.4 xl_arg_type
xl_arg_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])
Returns a decorator for registering a function for converting from a base type to a custom type.
Parameters
• name (string) – custom type name
• base_type (string) – base type
• allow_arrays (boolean) – custom type may be passed in an array using the standard
[] notation
• macro (boolean) – If True all functions using this type will automatically be registered
as a macro sheet equivalent function
• thread_safe (boolean) – If False any function using this type will never be regis-
tered as thread safe
4.1.5 xl_return_type
xl_return_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])
Returns a decorator for registering a function for converting from a custom type to a base type.
Parameters
• name (string) – custom type name
• base_type (string) – base type
• allow_arrays (boolean) – custom type may be returned as an array using the standard
[] notation
• macro (boolean) – If True all functions using this type will automatically be registered
as a macro sheet equivalent function
• thread_safe (boolean) – If False any function using this type will never be regis-
tered as thread safe
4.1.6 xl_arg
xl_arg(_name [, _type=None] [, **kwargs])
Decorator for providing type information for a function argument. This can be used instead of providing a
function signature to xl_func.
Parameters
• _name (string) – Argument name. This should match the argument name in the function
definition.
• _type – Optional argument type. This should be a recognized type name or the name of a
custom type.
• kwargs – Type parameters for parameterized types (eg NumPy arrays and Pandas types).
4.1. Function Decorators 74
PyXLL User Guide, Release 4.2.4
4.1.7 xl_return
xl_return([_type=None] [, **kwargs])
Decorator for providing type information for a function’s return value. This can be used instead of providing a
function signature to xl_func.
Parameters
• _type – Optional argument type. This should be a recognized type name or the name of a
custom type.
• kwargs – Type parameters for parameterized types (eg NumPy arrays and Pandas types).
4.2 Utility Functions
• reload
• rebind
• xl_app
• xl_version
• async_call
• get_config
• get_dialog_type
• get_last_error
• get_type_converter
• load_image
• cached_object_count
• get_event_loop
4.2.1 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.
4.2.2 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.
4.2. Utility Functions 75
PyXLL User Guide, Release 4.2.4
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()
4.2.3 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 using the requested COM package.
4.2.4 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
4.2.5 async_call
async_call(callable, *args, **kwargs)
Schedules a callable object (e.g. a function) in Excel’s main thread at some point in the (near) future. The
callable will be called from a macro context, meaning that it is generally safe to call back into Excel using
COM.
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).
4.2. Utility Functions 76
PyXLL User Guide, Release 4.2.4
Note that and Excel COM objects created in the one thread should not be used in another thread and doing so
may cause Excel to crash. Often the same thread will be used to call your worksheet function and run the async
callback, but in some cases they may be different. To be safe it is best to always obtain the Excel Application
object inside the callback function.
Parameters
• callable – Callable object to call in the near future.
• 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
4.2.6 get_config
get_config()
Returns the PyXLL config as a ConfigParser.SafeConfigParser instance
See also Configuring PyXLL.
4.2.7 get_dialog_type
get_dialog_type()
Returns
the type of the current dialog that initiated the call into the current Python function
xlDialogTypeNone
4.2. Utility Functions 77
PyXLL User Guide, Release 4.2.4
or xlDialogTypeFunctionWizard
or xlDialogTypeSearchAndReplace
xlDialogTypeNone = 0
xlDialogTypeFunctionWizard = 1
xlDialogTypeSearchAndReplace = 2
4.2.8 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]
4.2. Utility Functions 78
PyXLL User Guide, Release 4.2.4
xlcAlert(msg)
4.2.9 get_type_converter
get_type_converter(src_type, dest_type [, src_kwargs=None] [, dest_kwargs=None])
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) – Signature of type to convert from.
• dest_type (string) – Signature of type to convert to.
• src_kwargs (dict) – Parameters for the source type (e.g. {'dtype'=float} for
numpy_array).
• dest_kwargs (dict) – Parameters for the destination type (e.g. {'index'=True}
for dataframe).
Returns Function to convert from src_type to dest_type.
Example usage:
from pyxll import xl_func, get_type_converter
@xl_func("var x: var")
def py_function(x):
# if x is a number, convert it to a date
if isinstance(x, float):
to_date = get_type_converter("var", "date")
x = to_date(x)
return "%s : %s" % (x, type(x))
4.2.10 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"/>
4.2. Utility Functions 79
PyXLL User Guide, Release 4.2.4
</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.
4.2.11 cached_object_count
cached_object_count()
Returns the current number of cached objects.
When objects are returns from worksheet functions using the object or var type they are stored in an internal
object cache and a handle is returned to Excel. Once the object is no longer referenced in Excel the object is
removed from the cache automatically.
See Cached Python Objects.
4.2.12 get_event_loop
get_event_loop()
New in PyXLL 4.2
Get the async event loop used by PyXLL for scheduling async tasks.
If called in Excel and the event loop is not already running it is started.
If called outside of Excel then the event loop is returned without starting it.
Returns asyncio.AbstractEventLoop
See Asynchronous Functions.
4.3 Ribbon Functions
These functions can be used to manipulate the Excel ribbon.
The ribbon can be updated at any time, for example as PyXLL is loading via the xl_on_open and xl_on_reload
event handlers, or from a menu using using xl_menu.
See the section on customizing the ribbon for more details.
• get_ribbon_xml
• set_ribbon_xml
• set_ribbon_tab
• remove_ribbon_tab
4.3. Ribbon Functions 80
PyXLL User Guide, Release 4.2.4
4.3.1 get_ribbon_xml
get_ribbon_xml()
Returns the XML used to customize the Excel ribbon bar, as a string.
See the section on customizing the ribbon for more details.
4.3.2 set_ribbon_xml
set_ribbon_xml(xml, reload=True)
Sets the XML used to customize the Excel ribbon bar.
Parameters
• xml – XML to set, as a string.
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
See the section on customizing the ribbon for more details.
4.3.3 set_ribbon_tab
set_ribbon_tab(xml, tab_id=None, reload=True)
Sets a single tab in the ribbon using an XML fragment.
Instead of replacing the whole ribbon XML this function takes a tab element from the input XML and updates
the ribbon XML with that tab.
If multiple tabs exist in the input XML, the first who’s id attribute matches tab_id is used (or simply the first tab
element if tab_id is None).
If a tab already exists in the ribbon XML with the same id attribute then it is replaced, otherwise the new tab is
appended to the tabs element.
Parameters
• xml – XML document containing at least on tab element.
• tab_id – id of the tab element to set (or None to use the first tab element in the document).
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
4.3.4 remove_ribbon_tab
remove_ribbon_tab(tab_id, reload=True)
Removes a single tab from the ribbon XML where the tab element’s id attribute matches tab_id.
Parameters
• tab_id – id of the tab element to remove.
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
Returns True if a tab was removed, False otherwise.
4.3. Ribbon Functions 81
PyXLL User Guide, Release 4.2.4
4.4 Event Handlers
These decorators enable the user to register functions that will be called when certain events occur in the PyXLL addin.
• xl_on_open
• xl_on_reload
• xl_on_close
• xl_license_notifier
4.4.1 xl_on_open
xl_on_open(func)
Decorator for callbacks that should be called after PyXLL has been opened and the user modules have been
imported.
The callback takes a list of tuples of three three items: (modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info is the exception information (exc_type,
exc_value, exc_traceback).
Example usage:
from pyxll import xl_on_open
@xl_on_open
def on_open(import_info):
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
... do something with this error ...
4.4.2 xl_on_reload
xl_on_reload(func)
Decorator for callbacks that should be called after a reload is attempted.
The callback takes a list of tuples of three three items: (modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info is the exception information (exc_type,
exc_value, exc_traceback).
Example usage:
from pyxll import xl_on_reload, xlcCalculateNow
@xl_on_reload
def on_reload(reload_info):
for modulename, module, exc_info in reload_info:
if module is None:
4.4. Event Handlers 82
PyXLL User Guide, Release 4.2.4
exc_type, exc_value, exc_traceback = exc_info
... do something with this error ...
# recalcuate all open workbooks
xlcCalculateNow()
4.4.3 xl_on_close
xl_on_close(func)
Decorator for registering a function that will be called when Excel is about to close.
This can be useful if, for example, you’ve created some background threads and need to stop them cleanly for
Excel to shutdown successfully. You may have other resources that you need to release before Excel closes as
well, such as COM objects, that would prevent Excel from shutting down. This callback is the place to do that.
This callback is called when the user goes to close Excel. However, they may choose to then cancel the close
operation but the callback will already have been called. Therefore you should ensure that anything you clean
up here will be re-created later on-demand if the user decides to cancel and continue using Excel.
To get a callback when Python is shutting down, which occurs when Excel is finally quitting, you should
use the standard atexit Python module. Python will not shut down in some circumstances (e.g. when a non-
daemonic thread is still running or if there are any handles to Excel COM objects that haven’t been released) so
a combination of the two callbacks is sometimes required.
Example usage:
from pyxll import xl_on_close
@xl_on_close
def on_close():
print("closing...")
4.4.4 xl_license_notifier
xl_license_notifier(func)
Decorator for registering a function that will be called when PyXLL is starting up and checking the license key.
It can be used to alert the user or to email a support or IT person when the license is coming up for renewal, so
a new license can be arranged in advance to minimize any disruption.
The registered function takes 4 arguments: string name, datetime.date expdate, int days_left, bool is_perpetual.
If the license is perpetual (doesn’t expire) expdate will be the end date of the maintenance agreement (when
maintenance builds are available until) and days_left will be the days between the PyXLL build date and expdate.
Example usage:
from pyxll import xl_license_notifier
@xl_license_notifier
def my_license_notifier(name, expdate, days_left, is_perpetual):
if days_left < 30:
... do something here...
4.4. Event Handlers 83
PyXLL User Guide, Release 4.2.4
4.5 Excel C API Functions
PyXLL exposes certain functions from the Excel C API. These mostly should only be called from a worksheet, menu
or macro functions, and some should only be called from macro-sheet equivalent functions
1
.
• xlfCaller
• xlfSheetId
• xlfGetWorkspace
• xlfGetWorkbook
• xlfGetWindow
• xlfWindows
• xlfVolatile
• xlcAlert
• xlcCalculation
• xlcCalculateNow
• xlcCalculateDocument
• xlAsyncReturn
• xlAbort
• xlSheetNm
4.5.1 xlfCaller
xlfCaller()
Returns calling cell as an XLCell instance.
Callable from any function, but most properties of XLCell are only accessible from macro sheet equivalent
functions
1
4.5.2 xlfSheetId
xlSheetId(sheet_name)
Returns integer sheet id from a sheet name (e.g. ‘[Book1.xls]Sheet1’)
4.5.3 xlfGetWorkspace
xlfGetWorkspace(arg_num)
Parameters arg_num (int) – number of 1 to 72 specifying the type of workspace information to
return
Returns depends on arg_num
1
A macro sheet equivalent function is a function exposed using xl_func with macro=True.
4.5. Excel C API Functions 84
PyXLL User Guide, Release 4.2.4
4.5.4 xlfGetWorkbook
xlfGetWorkbook(arg_num workbook=None)
Parameters
• arg_num (int) – number from 1 to 38 specifying the type of workbook information to
return
• workbook (string) – workbook name
Returns depends on arg_num
4.5.5 xlfGetWindow
xlfGetWindow(arg_num, window=None)
Parameters
• arg_num (int) – number from 1 to 39 specifying the type of window information to return
• window (string) – window name
Returns depends on arg_num
4.5.6 xlfWindows
xlfWindows(match_type=0, mask=None)
Parameters
• match_type (int) – a number from 1 to 3 specifying the type of windows to match
1 (or omitted) = non-add-in windows
2 = add-in windows
3 = all windows
• mask (string) – window name mask
Returns list of matching window names
4.5.7 xlfVolatile
xlfVolatile(volatile)
Parameters volatile (bool) – boolean indicating whether the calling function is volatile or not.
Usually it is better to declare a function as volatile via the xl_func decorator. This function can be used to
make a function behave as a volatile or non-volatile function regardless of how it was declared, which can be
useful in some cases.
Callable from a macro equivalent function only
1
4.5. Excel C API Functions 85
PyXLL User Guide, Release 4.2.4
4.5.8 xlcAlert
xlcAlert(alert)
Pops up an alert window.
Callable from a macro or menu function only
1
Parameters alert (string) – text to display
4.5.9 xlcCalculation
xlcCalculation(calc_type)
set the calculation type to automatic or manual.
Callable from a macro or menu function only
1
Parameters calc_type (int) – xlCalculationAutomatic
or xlCalculationSemiAutomatic
or xlCalculationManual
xlCalculationAutomatic = 1
xlCalculationSemiAutomatic = 2
xlCalculationManual = 3
4.5.10 xlcCalculateNow
xlcCalculateNow()
recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are volatile
functions.
Equivalent to pressing F9.
Callable from a macro or menu function only
1
4.5.11 xlcCalculateDocument
xlcCalculateDocument()
recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are volatile
functions for the current worksheet only
Callable from a macro or menu function only
1
4.5.12 xlAsyncReturn
xlAsyncReturn(handle, value)
Used by asynchronous functions to return the result to Excel see Asynchronous Functions
This function can be called from any thread and doesn’t have to be from a macro sheet equivalent function
Parameters
• handle (object) – async handle passed to the worksheet function
• value (object) – value to return to Excel
4.5. Excel C API Functions 86
PyXLL User Guide, Release 4.2.4
4.5.13 xlAbort
xlAbort(retain=True)
Yields the processor to other tasks in the system and checks whether the user has pressed ESC to cancel a macro
or workbook recalculation.
Parameters retain (bool) – If False and a break condition has been set it is reset, otherwise
don’t change the break condition.
Returns True if the user has pressed ESC, False otherwise.
4.5.14 xlSheetNm
xlSheetNm(sheet_id)
Returns sheet name from a sheet id (as returned by xlSheetId or XLCell.sheet_id).
xlfGetDocument(arg_num[, name ])
Parameters
• arg_num (int) – number from 1 to 88 specifying the type of document information to
return
• name (string) – sheet or workbook name
Returns depends on arg_num
4.6 Classes
• RTD
• XLCell
• XLRect
• XLAsyncHandle
4.6.1 RTD
class RTD
RTD is a base class that should be derived from for use by functions wishing to return real time ticking data
instead of a static value.
See Real Time Data (RTD) for more information.
value
Current value. Setting the value notifies Excel that the value has been updated and the new value will be
shown when Excel refreshes.
connect(self )
Called when Excel connects to this RTD instance, which occurs shortly after an Excel function has returned
an RTD object.
May be overridden in the sub-class.
4.6. Classes 87
PyXLL User Guide, Release 4.2.4
@Since PyXLL 4.2.0: May be an async method.
disconnect(self )
Called when Excel no longer needs the RTD instance. This is usually because there are no longer any cells
that need it or because Excel is shutting down.
May be overridden in the sub-class.
@Since PyXLL 4.2.0: May be an async method.
set_error(exc_type, exc_value, exc_traceback)
Update Excel with an error. E.g.:
def update(self):
try:
self.value = get_new_value()
except:
self.set_error(
*
sys.exc_info())
4.6.2 XLCell
class XLCell
XLCell represents the data and metadata for a cell in Excel passed as an xl_cell argument to a function
registered with xl_func.
Some of the properties of XLCell instances can only be accessed if the calling function has been registered as
a macro sheet equivalent function
1
.
value
value of the cell argument, passed in the same way as the var type.
Must be called from a macro sheet equivalent function
1
address
string representing the address of the cell, or None if a value was passed to the function and not a cell
reference.
Must be called from a macro sheet equivalent function
1
formula
formula of the cell as a string, or None if a value was passed to the function and not a cell reference or
if the cell has no formula.
Must be called from a macro sheet equivalent function
1
note
note on the cell as a string, or None if a value was passed to the function and not a cell reference or if
the cell has no note.
Must be called from a macro sheet equivalent function
1
sheet_name
name of the sheet this cell belongs to.
sheet_id
integer id of the sheet this cell belongs to.
rect
XLRect instance with the coordinates of the cell.
1
A macro sheet equivalent function is a function exposed using xl_func with macro=True.
4.6. Classes 88
PyXLL User Guide, Release 4.2.4
is_calculated
True or False indicating whether the cell has been calculated or not. In almost all cases this will always be
True as Excel will automatically have recalculated the cell before passing it to the function.
Example usage:
from pyxll import xl_func
@xl_func("xl_cell cell: string", macro=True)
def xl_cell_test(cell):
return "[value=%s, address=%s, formula=%s, note=%s]" % (
cell.value,
cell.address,
cell.formula,
cell.note)
4.6.3 XLRect
class XLRect
XLRect instances are accessed via XLCell.rect to get the coordinates of the cell.
first_row
First row of the range as an integer.
last_row
Last row of the range as an integer.
first_col
First column of the range as an integer.
last_col
Last column of the range as an integer.
4.6.4 XLAsyncHandle
class XLAsyncHandle
XLAsyncHandle instances are passed to Asynchronous Functions as the async_handle argument.
They are passed to xlAsyncReturn to return the result from an asynchronous function.
set_value(value)
Set the value on the handle and return it to Excel.
Equivalent to xlAsyncReturn.
@Since PyXLL 4.2.0
set_error(exc_type, exc_value, exc_traceback)
Return an error to Excel.
@Since PyXLL 4.2.0
Example usage:
from pyxll import xl_func
import threading
import sys
@xl_func("async_handle h, int x")
4.6. Classes 89
PyXLL User Guide, Release 4.2.4
def async_func(h, x):
def thread_func(h, x):
try:
result = do_calculation(x)
h.set_value(result)
except:
result.set_error(
*
sys.exc_info())
thread = threading.Thread(target=thread_func, args=(h, x))
thread.start()
New in PyXLL 4.2
For Python 3.5.1 and later, asynchronous UDFs can be simplified by simply using the async keyword on the
function declaration and dropping the async_handle argument.
Async functions written in this way run in an asyncio event loop on a background thread.
4.6. Classes 90
CHAPTER 5
Examples
5.1 UDF Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Worksheet functions
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded. Functions are exposed
to Excel as worksheet functions by decorators declared in
the pyxll module.
Functions decorated with the xl_func decorator are exposed
to Excel as UDFs (User Defined Functions) and may be called
from cells in Excel.
"""
#
# 1) Basics - exposing functions to Excel
#
#
# xl_func is the main decorator and is used for exposing
# python functions to excel.
#
from pyxll import xl_func
#
# Decorating a function with xl_func is all that's required
# to make it callable in Excel as a worksheet function.
#
@xl_func
91
PyXLL User Guide, Release 4.2.4
def basic_pyxll_function_1(x, y, z):
"""returns (x
*
y)
**
z """
return (x
*
y)
**
z
#
# xl_func takes an optional signature of the function to be exposed to excel.
# There are a number of basic types that can be used in
# the function signature. These include:
# int, float, bool and string
# There are more types that we'll come to later.
#
@xl_func("int x, float y, bool z: float")
def basic_pyxll_function_2(x, y, z):
"""if z return x, else return y"""
if z:
# we're returning an integer, but the signature
# says we're returning a float.
# PyXLL will convert the integer to a float for us.
return x
return y
#
# You can change the category the function appears under in
# Excel by using the optional argument 'category'.
#
@xl_func(category="My new PyXLL Category")
def basic_pyxll_function_3(x):
"""docstrings appear as help text in Excel"""
return x
#
# 2) The var type
#
#
# A basic type is the var type. This can represent any
# of the basic types, depending on what type is passed to the
# function, or what type is returned.
#
# When no type information is given the var type is used.
#
@xl_func("var x: string")
def var_pyxll_function_1(x):
"""takes an float, bool, string, None or array"""
# we'll return the type of the object passed to us, pyxll
# will then convert that to a string when it's returned to
# excel.
return type(x)
#
# If var is the return type. PyXll will convert it to the
5.1. UDF Examples 92
PyXLL User Guide, Release 4.2.4
# most suitable basic type. If it's not a basic type and
# no suitable conversion can be found, it will be converted
# to a string and the string will be returned.
#
@xl_func("bool x: var")
def var_pyxll_function_2(x):
"""if x return string, else a number"""
if x:
return "var can be used to return different types"
return 123.456
#
# 3) Date and time types
#
#
# There are three date and time types: date, time, datetime
#
# Excel represents dates and times as floating point numbers.
# The pyxll datetime types convert the excel number to a
# python datetime.date, datetime.time and datetime.datetime
# object depending on what type you specify in the signature.
#
# dates and times may be returned using their type as the return
# type in the signature, or as the var type.
#
import datetime
@xl_func("date x: string")
def datetime_pyxll_function_1(x):
"""returns a string description of the date"""
return "type=%s, date=%s" % (type(x), x)
@xl_func("time x: string")
def datetime_pyxll_function_2(x):
"""returns a string description of the time"""
return "type=%s, time=%s" % (type(x), x)
@xl_func("datetime x: string")
def datetime_pyxll_function_3(x):
"""returns a string description of the datetime"""
return "type=%s, datetime=%s" % (type(x), x)
@xl_func("datetime[][] x: datetime")
def datetime_pyxll_function_4(x):
"""returns the max datetime"""
m = datetime.datetime(1900, 1, 1)
for row in x:
m = max(m, max(row))
return m
#
5.1. UDF Examples 93
PyXLL User Guide, Release 4.2.4
# 4) xl_cell
#
# The xl_cell type can be used to receive a cell
# object rather than a plain value. The cell object
# has the value, address, formula and note of the
# reference cell passed to the function.
#
# The function must be a macro sheet equivalent function
# in order to access the value, address, formula and note
# properties of the cell.
#
@xl_func("xl_cell cell : string", macro=True)
def xl_cell_example(cell):
"""a cell has a value, address, formula and note"""
return "[value=%s, address=%s, formula=%s, note=%s]" % (cell.value,
cell.address,
cell.formula,
cell.note)
5.2 Pandas Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Pandas
This module contains example functions that show how pandas DataFrames and Series
can be passed to and from Excel to Python functions using PyXLL.
Pandas needs to be installed for this example to work correctly.
See also the included examples.xlsx file.
"""
from pyxll import xl_func
@xl_func(volatile=True)
def pandas_is_installed():
"""returns True if pandas is installed"""
try:
import pandas
return True
except ImportError:
return False
@xl_func("int, int: dataframe<index=True>", auto_resize=True)
def random_dataframe(rows, columns):
"""
Creates a DataFrame of random numbers.
:param rows: Number of rows to create the DataFrame with.
5.2. Pandas Examples 94
PyXLL User Guide, Release 4.2.4
:param columns: Number of columns to create the DataFrame with.
"""
import pandas as pa
import numpy as np
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
df = pa.DataFrame(data, columns=column_names)
return df
@xl_func("dataframe<index=True>, float[], str[], str[]: dataframe<index=True>", auto_
˓→resize=True)
def describe_dataframe(df, percentiles=[], include=[], exclude=[]):
"""
Generates descriptive statistics that summarize the central tendency, dispersion
˓→and shape of a dataset's
distribution, excluding NaN values.
:param df: DataFrame to describe.
:param percentiles: The percentiles to include in the output. All should fall
˓→between 0 and 1.
:param include: dtypes to include.
:param exclude: dtypes to exclude.
:return:
"""
# filter out any blanks
percentiles = list(filter(None, percentiles))
include = list(filter(None, include))
exclude = list(filter(None, exclude))
return df.describe(percentiles=percentiles or None,
include=include or None,
exclude=exclude or None)
5.3 Cached Objects Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Object Cache Example
This module contains example functions that make use of the PyXLL
object cache.
When Python objects that can't be transformed into a basic type that
Excel can display are returned, PyXLL inserts them into a global
object cache and returns a reference id for the object. When this reference
id is passed to another PyXLL function the object is retrieved from the
cache and passed to the PyXLL function.
PyXLL keeps track of uses of the cached objects and removes items from the
5.3. Cached Objects Examples 95
PyXLL User Guide, Release 4.2.4
cache when they are no longer needed.
See also the included examples.xlsx file.
"""
from pyxll import xl_func
class MyTestClass(object):
"""A basic class for testing the cached_object type"""
def __init__(self, x):
self.__x = x
def __str__(self):
return "%s(%s)" % (self.__class__.__name__, self.__x)
@xl_func("var: object")
def cached_object_return_test(x):
"""returns an instance of MyTestClass"""
return MyTestClass(x)
@xl_func("object: string")
def cached_object_arg_test(x):
"""takes a MyTestClass instance and returns a string"""
return str(x)
class MyDataGrid(object):
"""
A second class for demonstrating cached_object types.
This class is constructed with a grid of data and has
some basic methods which are also exposed as worksheet
functions.
"""
def __init__(self, grid):
self.__grid = grid
def sum(self):
"""returns the sum of the numbers in the grid"""
total = 0
for row in self.__grid:
total += sum(row)
return total
def __len__(self):
total = 0
for row in self.__grid:
total += len(row)
return total
def __str__(self):
return "%s(%d values)" % (self.__class__.__name__, len(self))
@xl_func("float[][]: object")
5.3. Cached Objects Examples 96
PyXLL User Guide, Release 4.2.4
def make_datagrid(x):
"""returns a MyDataGrid object"""
return MyDataGrid(x)
@xl_func("object: int")
def datagrid_len(x):
"""returns the length of a MyDataGrid object"""
return len(x)
@xl_func("object: float")
def datagrid_sum(x):
"""returns the sum of a MyDataGrid object"""
return x.sum()
@xl_func("object: string")
def datagrid_str(x):
"""returns the string representation of a MyDataGrid object"""
return str(x)
5.4 Custom Type Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Custom types
Worksheet functions can use a number of standard types
as shown in the worksheetfuncs example.
It's also possible to define custom types that
can be used in the PyXLL function signatures
as shown by these examples.
For a more complicated custom type example see the
object cache example.
"""
#
# xl_arg_type and xl_return type are decorators that can
# be used to declare types that our excel functions
# can use in addition to the standard types
#
from pyxll import xl_func, xl_arg_type, xl_return_type
#
# 1) Custom types
#
#
# All variables are passed to and from excel as the basic types,
5.4. Custom Type Examples 97
PyXLL User Guide, Release 4.2.4
# but it's possible to register conversion functions that will
# convert those basic types to whatever types you like before
# they reach your function, (or after you function returns them
# in the case of returned values).
#
#
# CustomType1 is a very simple class used to demonstrate
# custom types.
#
class CustomType1:
def __init__(self, name):
self.name = name
def greeting(self):
return "Hello, my name is %s" % self.name
#
# To use CustomType1 as an argument to a pyxll function you have to
# register a function to convert from a basic type to our custom type.
#
# xl_arg_type takes two arguments, the new custom type name, and the
# base type.
#
@xl_arg_type("custom1", "string")
def string_to_custom1(name):
return CustomType1(name)
#
# now the type 'custom1' can be used as an argument type
# in a function signature.
#
@xl_func("custom1 x: string")
def customtype_pyxll_function_1(x):
"""returns x.greeting()"""
return x.greeting()
#
# To use CustomType1 as a return type for a pyxll function you have
# to register a function to convert from the custom type to a basic type.
#
# xl_return_type takes two arguments, the new custom type name, and
# the base type.
#
@xl_return_type("custom1", "string")
def custom1_to_string(x):
return x.name
#
# now the type 'custom1' can be used as the return type.
#
@xl_func("custom1 x: custom1")
def customtype_pyxll_function_2(x):
5.4. Custom Type Examples 98
PyXLL User Guide, Release 4.2.4
"""check the type and return the same object"""
assert isinstance(x, CustomType1), "expected an CustomType1 object"""
return x
#
# CustomType2 is another example that caches its instances
# so they can be referred to from excel functions.
#
class CustomType2:
__instances__ = {}
def __init__(self, name, value):
self.value = value
self.id = "%s-%d" % (name, id(self))
# overwrite any existing instance with self
self.__instances__[name] = self
def getValue(self):
return self.value
@classmethod
def getInstance(cls, id):
name, unused = id.split("-")
return cls.__instances__[name]
def getId(self):
return self.id
@xl_arg_type("custom2", "string")
def string_to_custom2(x):
return CustomType2.getInstance(x)
@xl_return_type("custom2", "string")
def custom2_to_string(x):
return x.getId()
@xl_func("string name, float value: custom2")
def customtype_pyxll_function_3(name, value):
"""returns a new CustomType2 object"""
return CustomType2(name, value)
@xl_func("custom2 x: float")
def customtype_pyxll_function_4(x):
"""returns x.getValue()"""
return x.getValue()
#
# custom types may be base types of other custom types, as
# long as the ultimate base type is a basic type.
#
# This means you can chain conversion functions together.
5.4. Custom Type Examples 99
PyXLL User Guide, Release 4.2.4
#
class CustomType3:
def __init__(self, custom2):
self.custom2 = custom2
def getValue(self):
return self.custom2.getValue()
*
2
@xl_arg_type("custom3", "custom2")
def custom2_to_custom3(x):
return CustomType3(x)
@xl_return_type("custom3", "custom2")
def custom3_to_custom2(x):
return x.custom2
#
# when converting from an excel cell to a CustomType3 object,
# the string will first be used to get a CustomType2 object
# via the registed function string_to_custom2, and then
# custom2_to_custom3 will be called to get the final
# CustomType3 object.
#
@xl_func("custom3 x: float")
def customtype_pyxll_function_5(x):
"""return x.getValue()"""
return x.getValue()
5.5 Menu Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Menus
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded.
Menus can be added to Excel via the pyxll xl_menu decorator.
"""
import traceback
import logging
_log = logging.getLogger(__name__)
# the webbrowser module is used in an example to open the log file
try:
import webbrowser
except ImportError:
5.5. Menu Examples 100
PyXLL User Guide, Release 4.2.4
_log.warning("
***
webbrowser could not be imported
***
")
_log.warning("
***
the menu examples will not work correctly
***
")
import os
#
# 1) Basics - adding a menu items to Excel
#
#
# xl_menu is the decorator used for addin menus to Excel.
#
from pyxll import xl_menu, get_config, xl_app, xl_version, get_last_error, xlcAlert
#
# The only required argument is the menu item name.
# The example below will add a new menu item to the
# addin's default menu.
#
@xl_menu("Example Menu Item 1")
def on_example_menu_item_1():
xlcAlert("Hello from PyXLL")
#
# menu items are normally sorted alphabetically, but the order
# keyword can be used to influence the ordering of the items
# in a menu.
#
# The default value for all sort keyword arguments is 0, so positive
# values will result in the item appearing further down the list
# and negative numbers result in the item appearing further up.
#
@xl_menu("Another example menu item", order=1)
def on_example_menu_item_2():
xlcAlert("Hello again from PyXLL")
#
# It's possible to add items to menus other than the default menu.
# The example below creates a new menu called 'My new menu' with
# one item 'Click me' in it.
#
# The menu_order keyword is optional, but may be used to influence
# the order that the custom menus appear in.
#
@xl_menu("Click me", menu="PyXLL example menu", menu_order=1)
def on_example_menu_item_3():
xlcAlert("Adding multiple menus is easy")
#
# 2) Sub-menus
#
# it's possible to add sub-menus just by using the sub_menu
# keyword argument. The example below adds a new sub menu
# 'Sub Menu' to the default menu.
5.5. Menu Examples 101
PyXLL User Guide, Release 4.2.4
#
# The order keyword argument affects where the sub menu will
# appear in the parent menu, and the sub_order keyword argument
# affects where the item will appear in the sub menu.
#
@xl_menu("Click me", sub_menu="More Examples", order=2)
def on_example_submenu_item_1():
xlcAlert("Sub-menus can be created easily with PyXLL")
#
# When using Excel 2007 and onwards the Excel functions accept unicode strings
#
@xl_menu("Unicode Test", sub_menu="More Examples")
def on_unicode_test():
xlcAlert(u"\u01d9ni\u0186\u020dde")
#
# A simple menu item to show how to get the PyXLL config
# object and open the log file.
#
@xl_menu("Open log file", order=3)
def on_open_logfile():
# the PyXLL config is accessed as a ConfigParser.ConfigParser object
config = get_config()
if config.has_option("LOG", "path") and config.has_option("LOG", "file"):
path = os.path.join(config.get("LOG", "path"), config.get("LOG", "file"))
webbrowser.open("file://%s" % path)
#
# If a cell returns an error it is written to the log file
# but can also be retrieved using 'get_last_error'.
# This menu item displays the last error captured for the
# current active cell.
#
@xl_menu("Show last error")
def show_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)
5.6 Macros and Excel Scripting
All examples are included in the PyXLL download.
Plain text version
5.6. Macros and Excel Scripting 102
PyXLL User Guide, Release 4.2.4
"""
PyXLL Examples: Automation
PyXLL worksheet and menu functions can call back into Excel
using the Excel COM API
*
.
In addition to the COM API there are a few Excel functions
exposed via PyXLL that allow you to query information about
the current state of Excel without using COM.
Excel uses different security policies for different types
of functions that are registered with it. Depending on
the type of function, you may or may not be able to make
some calls to Excel.
Menu functions and macros are registered as 'commands'.
Commands are free to call back into Excel and make changes to
documents. These are equivalent to the VBA Sub routines.
Worksheet functions are registered as 'functions'. These
are limited in what they can do. You will be able to
call back into Excel to read values, but not change
anything. Most of the Excel functions exposed via PyXLL
will not work in worksheet functions. These are equivalent
to VBA Functions.
There is a third type of function - macro-sheet equivalent
functions. These are worksheet functions that are allowed to
do most things a macro function (command) would be allowed
to do. These shouldn't be used lightly as they may break
the calculation dependencies between cells if not
used carefully.
*
Excel COM support was added in Office 2000. If you are
using an earlier version these COM examples won't work.
"""
import pyxll
from pyxll import xl_menu, xl_func, xl_macro
import logging
_log = logging.getLogger(__name__)
#
# Getting the Excel COM object
#
# PyXLL has a function 'xl_app'. This returns the Excel application
# instance either as a win32com.client.Dispatch object or a
# comtypes object (which com package is used may be set in the
# config file). The default is to use win32com.
#
# It is better to use this than
# win32com.client.Dispatch("Excel.Application")
# as it will always be the correct handle - ie the handle
# to the correct instance of Excel.
#
# For more information on win32com see the pywin32 project
# on sourceforge.
5.6. Macros and Excel Scripting 103
PyXLL User Guide, Release 4.2.4
#
# The Excel object model is the same from COM as from VBA
# so usually it's straightforward to write something
# in python if you know how to do it in VBA.
#
# For more information about the Excel object model
# see MSDN or the object browser in the Excel VBA editor.
#
from pyxll import xl_app
#
# A simple example of a menu function that modifies
# the contents of the selected range.
#
@xl_menu("win32com test", sub_menu="More Examples")
def win32com_menu_test():
# get the current selected range and set some text
selection = xl_app().Selection
selection.Value = "Hello!"
pyxll.xlcAlert("Some text has been written to the current cell")
#
# Macros can also be used to call back into Excel when
# a control is activated.
#
# These work in the same way as VBA macros, you just assign
# them to the control in Excel by name.
#
@xl_macro
def button_example():
xl = xl_app()
range = xl.Range("button_output")
range.Value = range.Value + 1
@xl_macro
def checkbox_example():
xl = xl_app()
check_box = xl.ActiveSheet.CheckBoxes(xl.Caller)
if check_box.Value == 1:
xl.Range("checkbox_output").Value = "CHECKED"
else:
xl.Range("checkbox_output").Value = "Click the check box"
@xl_macro
def scrollbar_example():
xl = xl_app()
caller = xl.Caller
scrollbar = xl.ActiveSheet.ScrollBars(xl.Caller)
xl.Range("scrollbar_output").Value = scrollbar.Value
#
# Worksheet functions can also call back into Excel.
#
5.6. Macros and Excel Scripting 104
PyXLL User Guide, Release 4.2.4
# The function 'async_call' must be used to do the
# actual work of calling back into Excel from another
# thread, otherwise Excel may lock waiting for the function
# to complete before allowing the COM object to modify the
# sheet, which will cause a dead-lock.
#
# To be able to call xlfCaller from the worksheet function,
# the function must be declared as a macro sheet equivalent
# function by passing macro=True to xl_func.
#
# If your function modifies the Excel worksheet it will
# trigger a recalculation so you have to take care not to
# cause an infinite loop.
#
# Accessing the 'address' property of the XLCell returned
# by xlfCaller requires this function to be a macro sheet
# equivalent function.
#
@xl_func(macro=True)
def automation_example(rows, cols, value):
"""copies value to a range of rows x cols below the calling cell"""
# get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
# the update is done asynchronously so as not to block some
# versions of Excel by updating the worksheet from a worksheet function
def update_func():
xl = xl_app()
range = xl.Range(address)
# get the cell below and expand it to rows x cols
range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))
# and set the range's value
range.Value = value
# kick off the asynchronous call the update function
pyxll.async_call(update_func)
return address
5.7 Event Handler Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Callbacks
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded.
5.7. Event Handler Examples 105
PyXLL User Guide, Release 4.2.4
Moldules can register callbacks with PyXLL that will be
called at various times to inform the user code of
certain events.
"""
from pyxll import xl_on_open, \
xl_on_reload, \
xl_on_close, \
xl_license_notifier, \
xlcAlert, \
xlcCalculateNow
import logging
_log = logging.getLogger(__name__)
@xl_on_open
def on_open(import_info):
"""
on_open is registered to be called by PyXLL when the addin
is opened via the xl_on_open decorator.
This happens each time Excel starts with PyXLL installed.
"""
# Check to see which modules didn't import correctly.
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
_log.error("Error loading '%s' : %s" % (modulename, exc_value))
@xl_on_reload
def on_reload(import_info):
"""
on_reload is registered to be called by PyXLL whenever a
reload occurs via the xl_on_reload decorator.
"""
# Check to see if any modules didn't import correctly.
errors = 0
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
_log.error("Error loading '%s' : %s" % (modulename, exc_value))
errors += 1
# Report if everything reloaded OK.
# If there are errors they will be dealt with by the error_handler.
if errors == 0:
xlcAlert("Everything reloaded OK!\n\n(Message from callbacks.py example)")
# Recalculate all open workbooks.
xlcCalculateNow()
@xl_on_close
def on_close():
"""
on_close will get called as Excel is about to close.
This is a good time to clean up any globals and stop
5.7. Event Handler Examples 106
PyXLL User Guide, Release 4.2.4
any background threads so that the python interpretter
can be closed down cleanly.
The user may cancel Excel closing after this has been
called, so your code should make sure that anything
that's been cleaned up here will get recreated again
if it's needed.
"""
_log.info("callbacks.on_close: PyXLL is closing")
@xl_license_notifier
def license_notifier(name, expdate, days_left, is_perpetual):
"""
license_notifier will be called when PyXLL is starting up, after
it has read the config and verified the license.
If there is no license name will be None and days_left will be less than 0.
"""
if days_left >= 0 or is_perpetual:
_log.info("callbacks.license_notifier: "
"This copy of PyXLL is licensed to %s" % name)
if not is_perpetual:
_log.info("callbacks.license_notifier: "
"%d days left before the license expires (%s)" % (days_left,
˓→expdate))
elif expdate is not None:
_log.info("callbacks.license_notifier: License key expired on %s" % expdate)
else:
_log.info("callbacks.license_notifier: Invalid license key")
5.7. Event Handler Examples 107
Index
A
address (XLCell attribute), 88
async_call() (in module pyxll), 76
C
cached_object_count() (in module pyxll), 80
connect() (RTD method), 87
D
disconnect() (RTD method), 88
F
first_col (XLRect attribute), 89
first_row (XLRect attribute), 89
formula (XLCell attribute), 88
G
get_config() (in module pyxll), 77
get_dialog_type() (in module pyxll), 77
get_event_loop() (in module pyxll), 80
get_last_error() (in module pyxll), 78
get_ribbon_xml() (in module pyxll), 81
get_type_converter() (in module pyxll), 79
I
is_calculated (XLCell attribute), 88
L
last_col (XLRect attribute), 89
last_row (XLRect attribute), 89
load_image() (in module pyxll), 79
N
note (XLCell attribute), 88
R
rebind() (in module pyxll), 75
rect (XLCell attribute), 88
reload() (in module pyxll), 75
remove_ribbon_tab() (in module pyxll), 81
RTD (class in pyxll), 87
S
set_error() (RTD method), 88
set_error() (XLAsyncHandle method), 89
set_ribbon_tab() (in module pyxll), 81
set_ribbon_xml() (in module pyxll), 81
set_value() (XLAsyncHandle method), 89
sheet_id (XLCell attribute), 88
sheet_name (XLCell attribute), 88
V
value (RTD attribute), 87
value (XLCell attribute), 88
X
xl_app() (in module pyxll), 76
xl_arg() (in module pyxll), 74
xl_arg_type() (in module pyxll), 74
xl_func() (in module pyxll), 70
xl_license_notifier() (in module pyxll), 83
xl_macro() (in module pyxll), 73
xl_menu() (in module pyxll), 72
xl_on_close() (in module pyxll), 83
xl_on_open() (in module pyxll), 82
xl_on_reload() (in module pyxll), 82
xl_return() (in module pyxll), 75
xl_return_type() (in module pyxll), 74
xl_version() (in module pyxll), 76
xlAbort() (in module pyxll), 87
XLAsyncHandle (class in pyxll), 89
xlAsyncReturn() (in module pyxll), 86
xlcAlert() (in module pyxll), 86
xlcCalculateDocument() (in module pyxll), 86
xlcCalculateNow() (in module pyxll), 86
xlcCalculation() (in module pyxll), 86
XLCell (class in pyxll), 88
108
PyXLL User Guide, Release 4.2.4
xlfCaller() (in module pyxll), 84
xlfGetDocument() (in module pyxll), 87
xlfGetWindow() (in module pyxll), 85
xlfGetWorkbook() (in module pyxll), 85
xlfGetWorkspace() (in module pyxll), 84
xlfVolatile() (in module pyxll), 85
xlfWindows() (in module pyxll), 85
XLRect (class in pyxll), 89
xlSheetId() (in module pyxll), 84
xlSheetNm() (in module pyxll), 87
Index 109
