Variable and Keyword Arguments¶

Variable Arguments (*args)¶

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].

Keyword Arguments (**kwargs)¶

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)

Footnotes