Skip to content

Behaviour

Work In Progress

This pags is currently a work in progress. Please read at your own risk

Introduction

xlpro aims to be as similar to plain Python development as possible, such that the user isn't bound by a proprietary codebase. However, the quirks of integrating function calling and visualisation within Excel require some compromises which have to strike a balance of convenience, readability, performance and syntax.

As a result, there are some behaviours worth noting to the user, which we hope only require minor adjustments.

Overview of Behaviours

  1. String values stored in the Excel cells embed information.

    • "PyObj<...>" is a reference to an arbitrary Python object.
    • "Promise<...>" indicates that a cell is awaiting a return value from the server.
    • "...Exception(...)" or "...Error(...)" or any string representation of a Python exception indicates the Python function call raised an Exception, and you should debug the file.

  2. xlpro can handle showing simple, array or image types.

    • Basic types, str, float, int, bool.
    • Numpy types, np.float64, np.int32 and others.
    • Datetime types np.DateTime, these are cast from and to Excel serial representation if formatted to Date in Excel.
    • numpy.ndarray and pandas.Dataframe structures including the above dtypes can be shown in Excel.
    • Images and matplotlib figures are supported with the show_image() method.

  3. All input arrays or values are checked for the string "pyNone" (Excel name =pyNone), which is replaced by Python None. This will cast numpy arrays to dtype=object.

  4. Types are cast using PEP 484 type hinting. E.g. a 2D Excel array is cast to a xlpro.ndarray1d, xlpro.ndarray1d[np.float64], or list1d to maintain behaviour between row or column vector inputs.

  5. Python functions with optional arguments are supported. Providing no input, or explcitly using the =pyEmpty Excel Name will use the default arguments.

  6. For convenience and to minimise disruption, A simple undo/redo mechanism is built into xlpro to mitigate xlpro wiping Excel's undo stack. The undo-tracker is queried periodically to ensure it is active in order to avoid mishaps (should the VBA context crash or reset).

  7. For convenience, lists or tuples of objects are returned as column vector spill arrays natively. Use xlpro.condense() around the return type if you wish to enforce the result does not expand.

Special Notes

Type Pre-Processing and Casting

xlpro wraps each function call coming from Excel with a type caster which is based on the type hints provided by the author in the function signature.

In its simplest form, this is necessary to cast the 2D array structure from Excel into predictable Python structures, not to mention the convenience of not needing to handle this in your function.

Note

If you want a function to be agnostic of a column or row vector in Excel, use a 1D data type hint, If you want to maintain the structure, use a 2D type hint.

The following built in types are provided as simple wrappers of the numpy.ndarray and list types.

from xlpro import (
    ndarray1d,
    ndarray2d,
    list1d,
    list2d,
)

EXPERIMENTAL - Complex casting

Numpy types will attempt to cast to xlpro.ndarray1d[np.float64] if provided as a type hint in the Python function signature, but this is experimental. If this doesn't give the desired behaviour, you could just use the xlpro.ndarray1d and cast using .astype(np.float64) at the beginning of your function.

def xl_array_to_npfloat64(arr:ndarray2d[np.float64])
    return arr # shape: (n, m), type: np.ndarray, dtype=np.float64

def xl_vector_to_npint32(arr:ndarray1d[np.int32])
    return arr # shape: (n,), type: np.ndarray, dtype=np.int32

Empty Cell Handling

The communication pipeline between Excel and Python is expensive. Some tricks are used to prevent this from being flooded unnecessarily.

xlpro generally completes an isReady check on all function inputs to prevent wasted calls. On inputs up to 32 cells in size, if any of the input values are strictly empty Excel cells, the function call assumes it is waiting for the cell to be populated from a spill array, for example. Strictly empty cells would pass an ISBLANK() check, but may be a null string ="".

The scenarios where isReady = False are as follows:

  1. The argument is a Promise (indicating the cell is waiting for the result).
  2. The argument looks like a Python Exception .
  3. The argument is an Excel error value.
  4. The argument is an empty cell.

Passing None from Excel to Python

Want to deliberately pass None to Python? - use the =pyNone formula in the cells in question. This string is converted to Python None in the backend.

A note on casting when using None

The use of None has implications on xlpro's type casting argument pre-processor.

The use of None in your formulas will not affect casts to xlpro.list1d or xlpro.list2d, but casts to xlpro.ndarray1d or xlpro.ndarray2d will cause the numpy array to convert to dtype=object. If this is undesired, handle the conversion back in the first line of your function body. See XXX

For arrays larger than 32 cells, Numpy's behaviour is natural and empty cells will cast to NaN for numeric arrays.

Why do this?

Excel issues ghost function calls on any intermediate calculation, e.g. a subfunction of a larger formula. These flood the calculation queue and hog resources. Removing these while still in VBA-land is cheap, and significantly improves the performance of xlpro's calculation cycle.

The Excel Undo Stack

xlpro is fundamentally a macro, and like any Excel macro which modifies the Workbook, any remote calls from the server which modify a cell immediately wipe the undo history of the workbook. This could mean that touching any input cell could immediately prevent you undoing your last command.

This behaviour is frankly always going to be dealbreaker for the user no matter how good the tool is otherwise. To mitigate the worst offending cases of this, xlpro includes a custom undo/redo mechanism which can handle basic cell formula changes, copy/pasting, and cut/pasting. This is intended to catch the case where you mistype over a cell which issues an xlpro-recalculation cycle and would otherwise be lost permanently.

If you plan on bulk changes, consider disabling automatic calculation.

Not all is lost

The native Excel undo stack is maintained where possible. Only by triggering a new/unique function call will the stack be wiped

VBA and Undo, a Very Common Problem

Managing the undo stack when dealing with VBA macros is a widely recognised limitation of Excel. xlpro's implementation is not perfect, but is effective if the sequence of selecting, typing, copying and pasting is kept simple. The hope is that this catches any small errors.