In Python it is possible to declare a function that takes a variable number of positional arguments using the special *args notation. These functions can be exposed to Excel as worksheet functions that also take a variable number of arguments.
The function shown below uses the first argument as a separator ond returns a string made up of the string values of all other arguments separated by the separator.
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))
You can also 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 [1].
New in PyXLL 5.8
Python functions can take an arbitrary number of named arguments using the special **kwargs argument.
A keyword argument is where you pass an argument to a function by name. When using **kwargs, any named argument that is not one of the function’s parameter names is added to a dictionary and passed to the function in the kwargs dictionary.
Excel does not support passing arguments by name when calling Excel worksheet functions (UDFs).
When registering a Python function to be called from Excel that has **kwargs the Excel function will expect a 2d array of values for the kwargs argument. PyXLL will convert that to a dictionary and pass that dictionary to the Python function as the **kwargs.
The array passed from Excel to the Python function should be a list of key, value pairs.
For example, the following Python function takes **kwargs and can be called from Excel passing a range of key, value pairs:
from pyxll import xl_func
@xl_func
def kwargs_example(**kwargs):
return "foo={foo}; bar={bar}".format(**kwargs)
The function can be called from Excel, passing the **kwargs as an array of key value pairs:
Arrays can be passed directly to Excel functions, without needing to refer to a range on a worksheet.
The syntax for this varies depending on your language settings. In English, the format to pass an array is
{ "key1", value1; "key2", value2; "keyN", valueN }
. ,
is the item separator, and ;
is the row
separator. In some languages the item and row separators are reversed.
Specifying types for **kwargs is done in the same way as for dictionay types. See Dictionary Types for full details of how to specify types for dictionaries.
If using Python 3.12 or higher, you can also use the standard TypedDict
type annotation for **kwargs.
from typing import TypedDict
from pyxll import xl_func
class MyTypedKwargs(TypedDict):
foo: int
bar: int
@xl_func
def kwargs_example(**kwargs: MyTypedKwargs) -> str:
return "foo={foo}; bar={bar}".format(**kwargs)
Note
It is not possible to combine *args and **kwargs for functions that are exposed to Excel. This is due to the constraints of how Excel functions can be called.
Footnotes
[1] | 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 may not be possible to register a function with 255 arguments because of the length limit on that string. |