Disabling Auto-Resizing of Dynamic Array Results in PyXLL

Why does my function automatically spill when returning a list with var?

When using the var return type in PyXLL, any Python list is treated as a range.

In modern versions of Excel that support Dynamic Arrays, ranges automatically spill into adjacent cells. This behavior cannot be disabled when returning a list with var.

For example:

@xl_func("int mode: var")
def scalar(mode):
    if mode == 1:
        return 1.2
    elif mode == 2:
        return "asd"
    else:
        return [1, 2, 3, "qwe"]

In Excel with Dynamic Arrays:

• 1.2 → Displays as a single cell (correct)

• "asd" → Displays as a single cell (correct)

• [1,2,3,"qwe"] → Automatically spills into multiple cells

This spilling behavior is built into Excel and cannot be overridden when returning a list as var.

Can I disable auto-resizing in Dynamic Array versions of Excel?

No.

The auto_resize option has no effect in modern Excel versions that support Dynamic Arrays. Excel will always expand returned ranges automatically.

The only Excel-side workaround is entering the formula with the @ operator (implicit intersection), but that returns only the first value — not the entire list as a single object.

Solution: Use the "object" return type instead

When using the "object" return type, Python values are returned to Excel as a cached object handle that can be passed to another Python function. Values in Excel look like "type@n". Using this, your list will display as a cached object handle instead of being spilled, and can be passed to other Python functions as a Python list object.

But, we don't want the same conversion to apply to simpler types like integers, floats, booleans and strings.

The solution is to use the "skip_primitives" type parameter to the object type, i.e. use "object<skip_primitives=True>" as the return type.

This allows:

• Primitive values (float, string, etc.) → Passed through directly

• Non-primitive values (like lists) → Returned as object handles

This prevents lists from being treated as ranges and spilling.

Example: using object<skip_primitives=True>

When using the object type with skip_primitives=True:

• Floats, strings, integers → Returned normally as scalar Excel values

• Lists, dicts, custom objects → Returned as PyXLL object handles

Example:

@xl_func("int mode: object<skip_primitives=True>")
def scalar(mode: int):
    if mode == 1:
        return 1.2
    elif mode == 2:
        return "asd"
    else:
        return [1, 2, 3, "qwe"]

Excel output:

• Mode 1 → 1.2

• Mode 2 → asd

• Mode 3 → <list object> (single cell, no spill)

Where can I find documentation about this behavior?

See the official PyXLL documentation section on mixing primitive values and objects:

https://www.pyxll.com/docs/userguide/udfs/cached-objects.html#mixing-primitive-values-and-objects