# Custom Functions

This tutorial teaches you everything about custom functions. Some highlights:

- [pandas]() and [Polars]() DataFrames as arguments and return values
- [Object handles]() to return Python objects to a single cell
- [Plots](): return Matplotlib figures as images
- [Streaming functions]() for live-updating values such as financial market data

## Basic syntax

The simplest custom function only requires the `@func` decorator:

```python
from xlwings import func

@func
def hello(name):
    return f"Hello {name}!"
```

In Excel, you can call this function by typing `=HELLO("World")` into a cell. You could also type `World` into cell `A1`, then reference that cell from the formula like so: `=HELLO(A1)`.

Custom functions can be defined in `main.py` or any other regular `.py` file in
the workbook. Custom function names need to be unique, so
defining the same function name in multiple Python modules results in a
registration error (Problems pane) that identifies both files. If a workbook function has the
same name as one in a [personal module](personal-modules.md), the workbook function takes precedence.

By default, function arguments that are single cells (like the `name` argument in the above example) arrive as a simple float, integer, boolean, or string. One-dimensional Excel ranges arrive as a list of values (e.g., `[1, 2]`), and two-dimensional ranges arrive as a list of lists (e.g., `[[1, 2], [3, 4]]`). For more details, see [Dimension of arguments](). The next section describes how to have the values arrive as pandas DataFrame instead.

## pandas DataFrames

By using the `@arg` and `@ret` decorators, you can apply converters and options to arguments and the return value, respectively.

For example, to read in the values of a range as pandas DataFrame and return the correlations without writing out the header and the index, you would write:

```python
import pandas as pd
from xlwings import func, arg, ret

@func
@arg("df", pd.DataFrame)
@ret(index=False, header=False)
def correl2(df):
    return df.corr()
```

## Polars DataFrames

Polars DataFrames work almost the same as pandas DataFrames. But since polars DataFrames don’t have an index and don’t support MultiIndex headers, the `index` option isn’t available and the `header` option only accepts `True` (default) or `False` (note that this example uses type hints, see [next section]()):

```python
import polars as pl

@xw.func
def myfunction(df: pl.DataFrame):
   # df is a polars DataFrame, do something with it
   return df
```

For an overview of the available converters and options, have a look at [Converters and Options](https://docs.xlwings.org/en/latest/converters.html).

## Using type hints instead of decorators

You can use type hints instead of or in combination with decorators:

```python
from xlwings import func
import pandas as pd

@func
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
    # df is a DataFrame, do something with it
    return df
```

In this example, the return type (`-> pd.DataFrame`) is optional, as xlwings automatically checks the type of the returned object.

If you need to provide additional conversion arguments, you can either provide them via an annotated type hint or via a decorator. Note that when you use type hints and decorators together, decorators override type hints for conversion.

To set `index=False` for both the argument and the return value, you can annotate the type hint like this:

```python
from typing import Annotated
from xlwings import func
import pandas as pd

@func
def myfunction(
    df: Annotated[pd.DataFrame, {"index": False}]
) -> Annotated[pd.DataFrame, {"index": False}]:
    # df is a DataFrame, do something with it
    return df
```

As this might be a little harder to read, you can extract the type definition, which also allows you to reuse it like so:

```python
from typing import Annotated
from xlwings import func
import pandas as pd

Df = Annotated[pd.DataFrame, {"index": False}]

@func
def myfunction(df: Df) -> Df:
    # df is a DataFrame, do something with it
    return df
```

Alternatively, you could also combine type hints with decorators:

```python
from typing import Annotated
from xlwings import func, arg, ret
import pandas as pd

@func
@arg("df", index=False)
@ret(index=False)
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
    # df is a DataFrame, do something with it
    return df
```

## Variable number of arguments (`*args`)

Varargs are supported. You can also use a converter, which will be applied to all arguments provided by `*args`:

```python
from xlwings import func, arg

@func
@arg("*args", pd.DataFrame, index=False)
def concat(*args):
    return pd.concat(args)
```

and the same with type hints:

```python
from typing import Annotated
from xlwings import func

@func
def concat(*args: Annotated[pd.DataFrame, {"index": False}]):
    return pd.concat(args)
```

## Doc strings

To describe your function and its arguments, you can use a function docstring or the `arg` decorator, respectively:

```python
from xlwings import func, arg

@func
@arg("name", doc='A name such as "World"')
def hello(name):
    """This is a classic Hello World example"""
    return f"Hello {name}!"
```

And again with type hints:

```python
from typing import Annotated
from xlwings import func

@func
def hello(name: Annotated[str, {"doc": 'A name such as "World"'}]):
    """This is a classic Hello World example"""
    return f"Hello {name}!"
```

These doc strings will appear in Excel’s function wizard/formula builder. Note that the name of the arguments will automatically be shown when typing the formula into a cell without having to do anything (intellisense).

## Enum parameters

If a string parameter only accepts a fixed set of values, you can declare it with `typing.Literal` so that Excel will show a dropdown of those values as the user types the function:

```python
from typing import Literal
from xlwings import func

@func
def get_planet(name: Literal["mercury", "venus", "earth"]) -> str:
    return name.title()
```

#### NOTE
Only string Literals show a dropdown. `Literal[1, 2, 3]` is accepted as a type hint but no dropdowns are shown in Excel.

To attach per-value tooltips (shown in Excel’s AutoComplete dropdown), wrap the `Literal` in `Annotated` with a `tooltips` dict:

```python
from typing import Annotated, Literal
from xlwings import func

@func
def get_planet_with_tooltips(
    name: Annotated[
        Literal["mercury", "venus", "earth"],
        {
            "tooltips": {
                "mercury": "Mercury is the first planet from the sun.",
                "venus": "Venus is the second planet from the sun.",
                "earth": "Earth is the third planet from the sun.",
            },
        },
    ],
) -> str:
    return name.title()
```

#### NOTE
- Tooltips aren’t shown on macOS.
- Requires xlwings 0.35.3+

## Date and time

Depending on whether you’re reading from Excel or writing to Excel, there are different tools available to work with date and time.

### Reading date and time

In the context of custom functions, xlwings will detect numbers, strings, and booleans but not cells with a date/time format. Hence, you need to use converters. For single datetime arguments do this:

```python
import datetime as dt
from xlwings import func

@func
@arg("date", dt.datetime)
def myfunc(date):
    return date
```

And again with type hints:

```python
import datetime as dt
from xlwings import func

@func
def myfunc(date: dt.datetime):
    return date
```

Instead of `dt.datetime`, you can also use `dt.date` to get a date object instead.

If you have multiple values that you need to convert, you can use the `xlwings.to_datetime()` function:

```python
import datetime as dt
import xlwings as xw
from xlwings import func

@func
def myfunc(dates):
    dates = [xw.to_datetime(d) for d in dates]
    return dates
```

And if you are dealing with pandas DataFrames, you can simply use the `parse_dates` option. It behaves the same as with `pandas.read_csv()`:

```python
import pandas as pd
from xlwings import func, arg

@func
@arg("df", pd.DataFrame, parse_dates=[0])
def timeseries_start(df):
    return df.index.min()
```

and again with type hints:

```python
from typing import Annotated
import pandas as pd
from xlwings import func

@func
def timeseries_start(df: Annotated[pd.DataFrame, {"parse_dates": [0]}]):
    return df.index.min()
```

Like `pandas.read_csv()`, you could also provide `parse_dates` with a list of columns names instead of indices.

### Writing date and time

When writing datetime object to Excel, xlwings automatically formats the cells as date if your version of Excel supports data types, so no special handling is required:

```python
import datetime as dt
import xlwings as xw
from xlwings import func

@func
def pytoday():
    return dt.date.today()
```

By default, it will format the date according to default format of Excel, but you can also override this by providing the `date_format` return option:

```python
import datetime as dt
import xlwings as xw
from xlwings import func

@func
@ret(date_format="yyyy-m-d")
def pytoday():
    return dt.date.today()
```

and again with type hints:

```python
import datetime as dt
import xlwings as xw
from xlwings import func

@func
def pytoday() -> Annotated[dt.date, {"date_format": "yyyy-m-d"}]:
    return dt.date.today()
```

For the accepted `date_format` string, consult the [official Excel documentation](https://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309).

#### NOTE
Some older builds of Excel don’t support date formatting and will display the date as date serial instead, requiring you format it manually.

## Custom function name

By default, a custom function appears in Excel under the upper-cased Python function name. Use the `name` argument if the function should appear under a different name (analogous to how it works with scripts):

```python
from xlwings import func

@func(name="helloName")
def hello_custom_name(name):
    return f"Hello {name}!"
```

This function will be shown as `helloName` in Excel. The name must start with a letter and may only contain letters, numbers, periods, and underscores (max. 128 characters).

#### NOTE
The `name` argument requires xlwings 0.36.12+ and is only supported by xlwings Lite and xlwings Server. The classic xlwings add-in on Windows ignores it and registers the function under its Python name.

## Namespace

A namespace groups related custom functions together by prepending the namespace to the function name, separated with a dot. For example, to have NumPy-related functions show up under the numpy namespace, you could do:

```python
import numpy as np
from xlwings import func

@func(namespace="numpy")
def standard_normal(rows, columns):
    rng = np.random.default_rng()
    return rng.standard_normal(size=(rows, columns))
```

This function will be shown as `NUMPY.STANDARD_NORMAL` in Excel.

To use the same namespace for all custom functions in a Python module, set
`__xlwings_func_namespace__` at module level:

```python
from xlwings import func

__xlwings_func_namespace__ = "numpy"

@func
def standard_normal(rows, columns):
    ...

@func
def mean(values):
    ...
```

These functions will be shown as `NUMPY.STANDARD_NORMAL` and `NUMPY.MEAN`. A
`namespace` passed directly to `@func` takes precedence over the module-level
namespace. Module-level namespaces require xlwings 0.36.13 or later.

To exclude an individual function from the module-level namespace, use
`@func(namespace="")`.

### Sub-namespace

You can create sub-namespaces by including a dot like so:

```python
@func(namespace="numpy.random")
```

This function will be shown as `NUMPY.RANDOM.STANDARD_NORMAL` in Excel.

## Help URL

You can include a link to a web page with more information about your function by using the `help_url` option. The function wizard/formula builder will show that link under “More help on this function”.

```python
from xlwings import func

@func(help_url="https://www.xlwings.org")
def hello(name):
    return f"Hello {name}!"
```

## Array Dimensions

If you want your function to accept arguments of any dimensions (as single cell or one- or two-dimensional ranges), you may need to use the `ndim` option to make your code work in every case. Likewise, you can return a simple list in a vertical orientation by using the `transpose` option.

### Dimension of arguments

Depending on the dimensionality of the function parameters, xlwings either delivers a scalar, a list, or a nested list:

- Single cells (e.g., `A1`) arrive as scalar, i.e., number, string, or boolean: `1` or `"text"`, or `True`
- A one-dimensional (vertical or horizontal!) range (e.g. `A1:B1` or `A1:A2`) arrives as list: `[1, 2]`
- A two-dimensional range (e.g., `A1:B2`) arrives as nested list: `[[1, 2], [3, 4]]`

This behavior is not only consistent in itself, it’s also in line with how NumPy works and is often what you want: for example, you can directly loop over a vertical 1-dimensional range of cells.

However, if the argument can be anything from a single cell to a one- or two-dimensional range, you’ll want to use the `ndim` option: this allows you to always get the inputs as a one- or two-dimensional list, no matter what the input dimension is:

```python
from xlwings import func, arg

@func
@arg("x", ndim=2)
def add_one(x):
    return [[cell + 1 for cell in row] for row in data]
```

and again with type hints:

```python
from typing import Annotated
from xlwings import func

@func
def add_one(x: Annotated[float, {"ndim": 2}]):
    return [[cell + 1 for cell in row] for row in data]
```

The above sample would raise an error if you’d leave away the `ndim=2` and use a single cell as argument `x`.

### Dimension of return value

If you need to write out a list in vertical orientation, the `transpose` option comes in handy:

```python
from xlwings import func, ret

@func
@ret(transpose=True)
def vertical_list():
    return [1, 2, 3, 4]
```

and again with type hints:

```python
from typing import Annotated
from xlwings import func

@func
def vertical_list() -> Annotated[list, {"transpose": True}]:
    return [1, 2, 3, 4]
```

## Error handling and error cells

When writing to Excel, error cells in Excel such as `#VALUE!` are used to display the Python error. When reading, xlwings turns error cells into `None` by default but optionally allows you to read them as strings. Let’s get into the details!

### Error handling

Whenever there’s an error in Python, the cell value will show `#VALUE!`. To understand what’s going on, click on the cell with the error, then hover (don’t click!) on the exclamation mark that appears: you’ll see the error message.

### Writing NaN values

`np.nan` and `pd.NA` will be converted to Excel’s `#NUM!` error type.

### Error cells

#### Reading error cells

By default, error cells such as `#VALUE!` are converted to `None` (scalars and lists) or `np.nan` (NumPy arrays and pandas DataFrames). If you’d like to get them in their string representation, use `err_to_str` option:

```python
from xlwings import func, arg

@func
@arg("x", err_to_str=True)
def myfunc(x):
    ...
```

and again with type hints:

```python
from typing import Annotated, Any
from xlwings import func

@func
def myfunc(x: Annotated[list[list[Any]], {"err_to_str"=True}):
    ...
```

#### Writing error cells

To format cells as proper error cells in Excel, simply use their string representation (`#DIV/0!`, `#N/A`, `#NAME?`, `#NULL!`, `#NUM!`, `#REF!`, `#VALUE!`):

```python
from xlwings import func

@func
def myfunc(x):
    return ["#N/A", "#VALUE!"]
```

#### NOTE
Some older versions of Excel don’t support proper error types and will display the error as string instead.

## Dynamic arrays

If your return value is a one- or two-dimensional array such as a list, NumPy array, or pandas DataFrame, Excel will automatically spill the values into the surrounding cells by using the native dynamic arrays. There are no code changes required:

Returning a simple list:

```python
from xlwings import func

@func
def programming_languages():
    return ["Python", "JavaScript"]
```

Returning a NumPy array with standard normally distributed random numbers:

```python
import numpy as np
from xlwings import func

@func
def standard_normal(rows, columns):
    rng = np.random.default_rng()
    return rng.standard_normal(size=(rows, columns))
```

Returning a pandas DataFrame:

```python
import pandas as pd
from xlwings import func

@func
def get_dataframe():
    df = pd.DataFrame({"Language": ["Python", "JavaScript"], "Year": [1991, 1995]})
    return df
```

## Volatile functions

Volatile functions are recalculated whenever Excel calculates something, even if none of the function arguments have changed. To mark a function as volatile, use the `volatile` argument in the `func` decorator:

```python
import datetime as dt
from xlwings import func

@func(volatile=True)
def last_calculated():
    return f"Last calculated: {dt.datetime.now()}"
```

## Object handles

Object handles allow you to return Python objects such as a pandas DataFrame to a single cell. Other custom functions can then use the cell with the object handle as a function argument for further manipulation. This functionality is especially helpful if you have huge amounts of data or if the object can’t be “translated” into Excel cells.

#### NOTE
Object handles require xlwings >= 0.36.5, make sure to update `requirements.txt` if you are using an older version.

![image](images/object_handles.png)

To make a custom function return an object handle, annotate the return value with `object`:

```python
from typing import Annotated
import pandas as pd
from xlwings import func, ret, ObjectHandle
from xlwings.constants import ObjectHandleIcons

@func
async def get_mymodel() -> object:
    return pd.DataFrame(
        {"A": [1, 2, 3, 4, 5], "B": [10, 8, 6, 4, 2], "C": [10, 9, 8, 7, 6]}
    )
```

By default, this will display an icon in the cell together with the data type of the object (cell `A1` in the screenshot). By clicking on the icon, you will get some info about that object. You can, however, add valuable information by specifying a different `text`, `icon`, and `properties` (the fields shown on the object handle’s card, cell `A3` in the screenshot). There are three ways to do this:

- via the `ret` decorator
- via an annotated type hint
- by wrapping the return value in an `ObjectHandle`, which additionally lets you customize them per object

Using the `ret` decorator:

```python
@func
@ret(
    icon=ObjectHandleIcons.table,
    text="My Model",
    properties={"Source": {"type": "String", "basicValue": "Model A"}},
)
async def get_mymodel() -> object:
    return pd.DataFrame(
        {"A": [1, 2, 3, 4, 5], "B": [10, 8, 6, 4, 2], "C": [10, 9, 8, 7, 6]}
    )
```

The `properties` follow the [Excel entity property](https://learn.microsoft.com/office/dev/add-ins/excel/excel-data-types-entity-card) format and, when provided, replace the automatically derived ones (such as the type and shape). To do the same via annotated type hint, you would do:

```python
MyModel = Annotated[
    object,
    {
        "icon": ObjectHandleIcons.table,
        "text": "My Model",
        "properties": {"Source": {"type": "String", "basicValue": "Model A"}},
    },
]

@func
async def get_mymodel() -> MyModel:
    return pd.DataFrame(
        {"A": [1, 2, 3, 4, 5], "B": [10, 8, 6, 4, 2], "C": [10, 9, 8, 7, 6]}
    )
```

If you instead want to customize them *per object*—for example, to show a text that depends on the value—wrap the return value in an `ObjectHandle`:

```python
from xlwings import func, ObjectHandle
from xlwings.constants import ObjectHandleIcons

@func
async def get_mymodel() -> object:
    df = load_model_data()
    if df.empty:
        return ObjectHandle(df, text="No data", icon=ObjectHandleIcons.warning)
    else:
        return ObjectHandle(
            df,
            text=f"{len(df)} rows",
            icon=ObjectHandleIcons.table,
        )
```

`ObjectHandle` accepts the wrapped object as the first argument, followed by the same optional `text`, `icon`, and `properties` keyword arguments. Values set via `ObjectHandle` take precedence over those set via `ret` or the annotated type hint.

To be able to use an object handle as argument in another function, annotate the argument with `CachedObject[...]`, where `...` is the type of the wrapped object:

```python
import pandas as pd
from xlwings import func, CachedObject

@func
async def df_query(df: CachedObject[pd.DataFrame], query: str):
    return df.query(query)  # df is a DataFrame as far as your editor is concerned
```

The subscript keeps the static type information inside the function, so editors and type checkers know that `df` is, for example, a `pd.DataFrame` (and offer autocomplete for `df.query`). If the function should accept any object handle, use a bare `CachedObject`:

```python
from xlwings import func, CachedObject

@func
async def view(obj: CachedObject):
    return obj
```

#### NOTE
You can also annotate the argument with `object` instead of `CachedObject[...]`. This works identically but loses the static type information inside the function.

If you are looking for functionality similar to how the `xl()` function works in Microsoft’s Python in Excel, you can do it as follows:

```python
from xlwings import arg, func

@func
@arg("df", index=False)
async def to_df(df: pd.DataFrame) -> object:
    return df
```

This turns an existing Excel range into a DataFrame. Using an Excel table as your source range is a good idea as it makes your object handle dynamically update whenever you resize the Excel table.

## Plots

Custom functions can return Matplotlib figures as images. Because this relies on the xlwings image cache in the cloud, it requires an internet connection (during calculation only). It is enabled by default—if you’d rather not have images uploaded, turn off **Allow custom functions to return images** under **xlwings Lite menu > Settings > Local**, and functions returning a figure will show an error in the cell instead. Only the image is uploaded, no raw data or code. After the function calculation completes, the image is embedded in the Excel workbook and deleted from the cache.

#### NOTE
[Self-Hosting](self-hosting.md) uses an internal image cache, so there’s no cloud involved.

Consider the following example, which returns a Matplotlib figure of a sine wave:

```python
import numpy as np
import matplotlib.pyplot as plt
from xlwings import func

@func
def plot_sine(frequency=1):
    x = np.linspace(0, 2 * np.pi, 500)
    y = np.sin(frequency * x)

    fig, ax = plt.subplots()
    ax.plot(x, y, color="green", linewidth=2)
    ax.fill_between(x, y, alpha=0.2, color="green")
    return fig
```

Calling `=PLOT_SINE()` in cell A2 returns the plot as an in-cell image:

![image](images/plot_sine.png)

To get the big plot as shown in the screenshot, right-click on cell A2, then select **Picture in Cell > Create Reference**:

![image](images/plot_create_ref.png)

With a big plot, this feels similar to a native Excel chart (change the value in cell A1 from `1` to `2` to see the plot automatically update). There are several libraries that use Matplotlib under the hood:

- pandas
- Seaborn
- Plotnine
- etc.

You can use all of them too, just make sure to return the underlying Matplotlib figure. Here’s a pandas example:

```python
import pandas as pd
from xlwings import func

@func
def plot_df():
    df = pd.DataFrame(
        {"A": [1, 3, 2, 4, 3], "B": [2, 4, 3, 5, 6]}
    )
    ax = df.plot()
    return ax.get_figure()
```

When opening a workbook with custom function plots, you will need to confirm the following security prompt:

![image](images/plot_enable.png)

If you don’t click **Enable Content**, a recalculation of the custom function will result in a `#BLOCKED!` error.

For more on plotting across custom functions, scripts, and the notebook, see [Plotting](plotting.md).

#### NOTE
Plots require xlwings>=0.36.8 in `requirements.txt`.

## Streaming functions (“RTD functions”)

In the traditional version of Excel, streaming functions were called “RTD functions” or “RealTimeData functions”. However, unlike traditional RTD functions, streaming functions don’t use a local COM server. Instead, the process runs as a background task and pushes updates to Excel.

To create a streaming function, you simply need to write an asynchronous generator. That is, you need to use `async def` instead of `def` and `yield` instead of `return`:

```python
import asyncio
import datetime

from xlwings import func

@func
async def streaming_clock():
    while True:
        yield f"{datetime.datetime.now():%H:%M:%S}"
        await asyncio.sleep(1)
```

Streaming functions also support 1d or 2d return values:

```python
import asyncio

import numpy as np
from xlwings import func


@func
async def streaming_random(rows=3, cols=4):
    rng = np.random.default_rng()
    while True:
        yield rng.standard_normal(size=(rows, cols))
        await asyncio.sleep(1)
```

For a more practical example, here’s how you can stream the BTC price from a REST API (make sure to add `httpx` to your `requirements.txt`):

```python
# Requires httpx in your requirements.txt
import asyncio

import httpx
from xlwings import func

@func
async def btc_price():
    async with httpx.AsyncClient() as client:
        while True:
            response = await client.get(
                "https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT"
            )
            response_data = response.json()
            yield float(response_data["price"])
            await asyncio.sleep(1)
```

Note that streaming functions run in the async world, so you need to use async libraries for I/O operations. For example, instead of using `requests`, use an async library such as `httpx` or `aiohttp`.

#### NOTE
Streaming functions require xlwings>=0.36.1 in `requirements.txt`.

## Accessing the calling cell

If your custom function needs to know which cell it was called from, add an argument with the `xw.Caller` type hint:

```python
import xlwings as xw
from xlwings import func


@func
def get_caller(caller: xw.Caller):
    return caller.address
```

`Caller` provides the following attributes:

| Attribute    | Example      | Description                        |
|--------------|--------------|------------------------------------|
| `address`    | `B21`        | A1 notation of the calling cell    |
| `row`        | `21`         | 1-based row of the calling cell    |
| `column`     | `2`          | 1-based column of the calling cell |
| `sheet_name` | `Sheet1`     | Name of the sheet                  |
| `book_name`  | `Book1.xlsx` | Name of the workbook               |

`Caller` describes *where* the function was called from: it isn’t an `xlwings.Range`
and doesn’t give you access to the cell’s values etc. A custom function only ever sees its
own arguments—it can’t read a cell that isn’t passed in as an argument, and it can’t
write anywhere other than its own result range. To interact with the Excel object model
after a custom function returns, use [`WithScript`]().

#### NOTE
- [Streaming functions]() don’t report the calling cell, so using the `xw.Caller` type hint raises an error.
- The `xw.Caller` type hint requires xlwings>=0.36.17.

## Running a script after a custom function

Custom functions can only write their return value—either into the calling cell or, for [dynamic arrays](), spilled into the surrounding cells. They can’t write anywhere else in the workbook. If you need such a side effect, return `WithScript()` to have a [script](custom-scripts.md) run after the function returns:

```python
import xlwings as xw
from xlwings import WithScript, func, script


@script
def hello_args(book: xw.Book, name: str, number: int):
    book.sheets[0]["A1"].value = f"{name} {number}"


@func
def hello_with_script(name):
    return WithScript(
        f"Hello {name}!",
        hello_args,
        args=[name, 42],
    )
```

The first argument is the value written to the cell. The second argument is the script function. `args` are handed to the script and must be JSON-serializable.

If the script lives in a different file, import it:

```python
import scripts

from xlwings import WithScript, func


@func
def hello_with_script(name):
    return WithScript(f"Hello {name}!", scripts.hello_args, args=[name, 42])
```

Combined with [`Caller`](), this lets you format the result range after a custom function returns:

```python
import pandas as pd
import xlwings as xw
from xlwings import Caller, WithScript, func, script


@script
def format_table(
    book: xw.Book,
    sheet_name: str,
    address: str,
    nrows: int,
    ncols: int,
):
    """Formats the range that the custom function spilled into."""
    sheet = book.sheets[sheet_name]
    table = sheet[address].resize(nrows, ncols)

    # Header row
    header = table[0, :]
    header.color = "#15a3a3"
    header.font.color = "#ffffff"
    header.font.bold = True

    # Body: number format on the numeric columns
    table[1:, 1:].number_format = "#,##0.00"


@func
def sales_report(caller: Caller):
    df = pd.DataFrame({
        "Product": ["Apples", "Bananas", "Cherries"],
        "Q1": [1000.5, 2300.75, 1750.25],
        "Q2": [1200.0, 2100.5, 1980.0],
    })
    df = df.set_index("Product")

    # +1 row/col for the header row and the index column
    nrows, ncols = df.shape[0] + 1, df.shape[1] + 1

    return WithScript(
        df,
        format_table,
        args=[caller.sheet_name, caller.address, nrows, ncols],
    )
```

Limitations:

- The script runs once per successful call of the custom function. If you fill the formula down 500 rows, the script runs 500 times. Also make sure that the script doesn’t write to cells that the custom function depends on, as this would cause an endless loop.
- Streaming functions are not supported.
- The script is run at the next calculation boundary. This requires ExcelApi 1.8; on older versions, it is run on a best-effort basis right after the custom function returns.

#### NOTE
`xw.WithScript` requires xlwings>=0.36.17.

## Asynchronous functions

Custom functions are always asynchronous, meaning that the cell will show `#BUSY!` during calculation, allowing you to continue using Excel: custom functions don’t block Excel’s user interface.

## Custom functions vs. classic UDFs

While xlwings Lite custom functions are mostly compatible with the VBA-based UDFs from classic xlwings, there are a few differences, which you should be aware of when switching from UDFs to custom functions or vice versa:

|                                                         | Custom functions (xlwings Lite)                                                       | User-defined functions UDFs (classic xlwings)            |
|---------------------------------------------------------|---------------------------------------------------------------------------------------|----------------------------------------------------------|
| Supported platforms                                     | - Windows<br/>- macOS<br/>- Excel on the web                                          | - Windows                                                |
| Empty cells are converted to                            | `0` => If you want `None`, you have to set the following formula in Excel: `=""`      | `None`                                                   |
| Cells with integers are converted to                    | Integers                                                                              | Floats                                                   |
| Reading Date/Time-formatted cells                       | Requires the use of `dt.datetime` or `parse_dates` in the arg decorators              | Automatic conversion                                     |
| Writing datetime objects                                | Automatic cell formatting                                                             | No cell formatting                                       |
| Can write proper Excel cell error                       | Yes                                                                                   | No                                                       |
| Writing `NaN` (`np.nan` or `pd.NA`) arrives in Excel as | `#NUM!`                                                                               | Empty cell                                               |
| Asynchronous functions                                  | Always and automatically                                                              | Requires `@xw.func(async_mode="threading")`              |
| Formula Intellisense                                    | Yes                                                                                   | No                                                       |
| Supports namespaces e.g., `NAMESPACE.FUNCTION`          | Yes                                                                                   | No                                                       |
| Capitalization of function name                         | Excel formula gets automatically capitalized                                          | Excel formula has same capitalization as Python function |
| `caller` function argument                              | N/A                                                                                   | Returns Range object of calling cell                     |
| `@xw.arg(vba=...)`                                      | N/A                                                                                   | Allows to access Excel VBA objects                       |
| Can return pictures                                     | Matplotlib figures only (see [Plotting](plotting.md)) | Yes                                                      |
| Requires a local installation of Python                 | No                                                                                    | Yes                                                      |

## Limitations

- You can’t define the same function name with xlwings Lite as you define elsewhere, e.g., in VBA or Lambda functions.
- Custom Functions were introduced in 2018 and therefore require at least Excel 2021 or Excel 365.
- Note that some functionality requires specific build versions, such as error cells and date formatting, but if your version of Excel doesn’t support these features, xlwings will fall back to either string-formatted error messages or unformatted date serials.