xlOil Python Registering Functions

There are several ways a python function can be registered with Excel via arguments to xloil.func decorator.

@xloil.func
def Greeting(who):
    return "Hello  " + who

Local Functions

When registering functions from Workbook Modules, xlOil defaults to making any declared functions “local”: this means their scope is limited to the workbook. It also means the function is automatically macro-type (xlOil achieves this by creating a VBA stub to invoke them).

This behaviour can be overriden by local argument:

@xloil.func(local=False)
def Greeting(who):
    return "Hello  " + who

Local functions have some limitations compared to global scope ones: - No native async or threadsafe, but RTD async is OK - Slower due to the VBA redirect - Associated workbook must be saved as macro-enabled (xlsm extension) - No function wizard help, but CTRL+SHIFT+A to show argument names is available

(Technical note: It is possible to use the Application.MacroOptions call to add help to the function wizard for VBA, but identically named functions will conflict which rather defeats the purpose of local functions).

Async and RTD Functions

RTD (real time data) functions are able to return values independently of Excel’s calculation cycle and correspond to async generators in python. For example, the function below returns the time every two seconds:

import xloil, datetime, asyncio

@xloil.func
async def pyClock():
    while True:
        await asyncio.sleep(2)
        yield datetime.datetime.now()

This is discussed in detail in Introduction.

Commands, Macros & Subroutines

‘Macros’ in VBA are declared as subroutines (Sub/End Sub) and do not return a value. These functions run outside the calculation cycle, triggered by some user interaction such as a button. They run on Excel’s main thread and have full permissions on the Excel object model. In the XLL interface, these are called ‘commands’ in the XLL interface and xlOil uses this terminology.

Programs which heavily use the Introduction object model are usually written as commands.

@xloil.func(command=True)
def pressRunTests():

    r = xloil.Range("TestArea")
    r.clear()
    r.set("Foo")

    ...

If not Local Functions, XLL commands are hidden and not displayed in dialog boxes for running macros, such as Excel’s macro viewer (Alt+F8). However their names can be entered anywhere a valid command name is required, including in the macro viewer.

Multi-threaded functions

Declaring a function re-entrant tells Excel it can be called on all of its calculation threads simultaneously - any other function is invoked on the main thread.

Local Functions cannot be declared re-entrant.

Since python (at least CPython) is single-threaded there is no direct performance benefit from enabling this. However, if you make frequent calls to C-based libraries speed gains may be possible.

import xloil, ctypes

@xloil.func(local=False, threaded=True)
def threadsafe(x: float) -> int:
    # Do lots of calculations
    ...
    # Return the thread ID to prove the functions were executed on different threads
    return ctypes.windll.kernel32.GetCurrentThreadId(None)

Dynamic Registration

Functions for registration can be specified at runtime without the need to decorate them with xloil.func.

Note

Although Excel will let you, avoid doing this from (non-async) worksheet functions since creating new functions during Excel’s calculation cycle is likely to cause instability.

The xloil.import_functions call provides an analogue of from X import Y as Z with Excel UDFs. A simple usage is:

xloil.import_functions("c:/lib/AnotherFile.py", names=["greet"], as_names=["PyGreet"])

where AnotherFile.py contains:

def greet(x:str):
    return f"Hello {x}!"

We specify the Excel name of the function explicitly, if we omitted this, the function would be registered with its python name. In Excel you can then use the formula =greet("World").

Typing annotations are respected, as are doc-strings - the import behaves as if we had decorated the function with xloil.func.

In a worksheet, xloil.import_functions is exposed as xloImport with the same arguments.

Since the import machinery can register any callable, including class constructors, you cane be a little creative. For example, the following cell formulae will create a pandas DataFrame from the range C1:F5, sum over rows and take the average of the result.

[A1] : =xloImport("pandas","DataFrame")

[A2] : =DataFrame(C1:F5)

[A3] : =xloAttr(xloAttrObj(A2,"sum",{"axis",1}), "mean")

Notice we used xloAttrObj - the output of this is always a cache reference. This stops xlOil from trying to convert the result to an Excel value. Since a DataFrame is iterable it would otherwise output DataFrame.index as an array. Also note the convenient use of array constants to specify keyword arguments.

More controlled registration

xloil.register_functions

  class Closure:
      self._total = 0
      def __call__(self, x):
          self._total += x
          return x

  funcs.append(
      xlo.func(fn=Closure(), name=f"dynamic1", register=False)
      )

  xlo.register_functions(funcs, sys.modules[__name__])

Loads functions from the specified source and registers them in Excel. The functions
      do not have to be decorated, but are imported as if they were decorated with ``xloil.func``.
      So if the functions have typing annotations, they are respected where possible.

      This function is intended

  Imports the specifed python module and registers any it for xloil
  functions it contains.  Leaving the argument blank loads or reloads the
  workbook module for the calling sheet, i.e. the file `WorkbookName.py`.