Function Decorators

These decorators are used to expose Python functions to Excel as worksheet functions, menu functions and macros.

xl_func

xl_func(signature=None, category=PyXLL, help_topic="", thread_safe=False, macro=False, allow_abort=None, volatile=False, disable_function_wizard_calc=False, disable_replace_calc=False, name=None, auto_resize=False)

xl_func is decorator used to expose python functions to Excel. Functions exposed in this way can be called from formulas in an Excel worksheet and appear in the Excel function wizzard.

Parameters:
  • signature (string) –

    string specifying the argument types and, optionally, their names and the return type. If the return type isn’t specified the var type is assumed. eg:

    "int x, string y: double" for a function that takes two arguments, x and y and returns a double.

    "float x" or "float x: var" for a function that takes a float x and returns a variant type.

    If no signature is provided the argument and return types will be inferred from any type annotations, and if there are no type annotations then the types will be assumed to be var.

    See Standard Types for the built-in types that can be used in the signature.

  • category (string) – String that sets the category in the Excel function wizard the exposed function will appear under.
  • help_topic (string) – Path of the help file (.chm) that will be available from the function wizard in Excel.
  • thread_safe (boolean) – Indicates whether the function is thread-safe or not. If True the function may be called from multiple threads in Excel 2007 or later
  • macro (boolean) – If True the function will be registered as a macro sheet equivalent function. Macro sheet equivalent functions are less restricted in what they can do, and in particular they can call Excel macro sheet functions such as xlfCaller.
  • allow_abort (boolean) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
  • volatile (boolean) – if True the function will be registered as a volatile function, which means it will be called everytime Excel recalculates regardless of whether any of the parameters to the function have changed or not
  • disable_function_wizard_calc (boolean) – Don’t call from the Excel function wizard. This is useful for functions that take a long time to complete that would otherwise make the function wizard unresponsive
  • disable_replace_calc (boolean) – Set to True to stop the function being called from Excel’s find and replace dialog.
  • arg_descriptions – dict of parameter names to help strings.
  • name (string) – The Excel function name. If None, the Python function name is used.
  • auto_resize (boolean) – When returining an array, PyXLL can automatically resize the range used by the formula to match the size of the result.

Example usage:

from pyxll import xl_func

@xl_func
def hello(name):
    """return a familiar greeting"""
    return "Hello, %s" % name

# Python 3 using type annotations
@xl_func
def hello2(name: str) -> str:
    """return a familiar greeting"""
    return "Hello, %s" % name

# Or a signature may be provided as string
@xl_func("int n: int", category="Math", thread_safe=True)
def fibonacci(n):
    """naive iterative implementation of fibonacci"""
    a, b = 0, 1
    for i in xrange(n):
        a, b = b, a + b
    return a

xl_menu

xl_menu(name, menu=None, sub_menu=None, order=0, menu_order=0, allow_abort=None, shortcut=None)

xl_menu is a decorator for creating menu items that call Python functions. Menus appear in the ‘Addins’ section of the Excel ribbon from Excel 2007 onwards, or as a new menu in the main menu bar in earlier Excel versions.

Parameters:
  • name (string) – name of the menu item that the user will see in the menu
  • menu (string) – name of the menu that the item will be added to. If a menu of that name doesn’t already exist it will be created. By default the PyXLL menu is used
  • sub_menu (string) – name of the submenu that this item belongs to. If a submenu of that name doesn’t exist it will be created
  • order (int) – influences where the item appears in the menu. The higher the number, the further down the list. Items with the same sort order are ordered lexographically. If the item is a sub-menu item, this order influences where the sub-menu will appear in the main menu. The menu order my also be set in the config (see configuration).
  • sub_order (int) – similar to order but it is used to set the order of items within a sub-menu
  • menu_order (int) – used when there are multiple menus and controls the order in which the menus are added
  • allow_abort (boolean) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
  • shortcut (string) –

    Assigns a keyboard shortcut to the menu item. Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For example, ‘Ctrl+Shift+R’.

    If the same key combination is already in use by Excel it may not be possible to assign a menu item to that combination.

Example usage:

from pyxll import xl_menu, xlcAlert

@xl_menu("My menu item")
def my_menu_item():
    xlcAlert("Menu button example")

xl_macro

xl_macro(signature=None, allow_abort=None, name=None, shortcut=None)

xl_macro is a decorator for exposing python functions to Excel as macros. Macros can be triggered from controls, from VBA or using COM.

Parameters:
  • signature (str) –

    An optional string that specifies the argument types and, optionally, their names and the return type.

    The format of the signature is identical to the one used by xl_func.

    If no signature is provided the argument and return types will be inferred from any type annotations, and if there are no type annotations then the types will be assumed to be var.

  • allow_abort (bool) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
  • name (string) – The Excel macro name. If None, the Python function name is used.
  • shortcut (string) –

    Assigns a keyboard shortcut to the macro. Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For example, ‘Ctrl+Shift+R’.

    If the same key combination is already in use by Excel it may not be possible to assign a macro to that combination.

    Macros can also have keyboard shortcuts assigned in the config file (see configuration).

Example usage:

from pyxll import xl_macro, xlcAlert

@xl_macro
def popup_messagebox():
    """pops up a message box"""
    xlcAlert("Hello")

@xl_macro
def py_strlen(s):
    """returns the length of s"""
    return len(s)

xl_arg_type

xl_arg_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])

Returns a decorator for registering a function for converting from a base type to a custom type.

Parameters:
  • name (string) – custom type name
  • base_type (string) – base type
  • allow_arrays (boolean) – custom type may be passed in an array using the standard [] notation
  • macro (boolean) – If True all functions using this type will automatically be registered as a macro sheet equivalent function
  • thread_safe (boolean) – If False any function using this type will never be registered as thread safe

xl_return_type

xl_return_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])

Returns a decorator for registering a function for converting from a custom type to a base type.

Parameters:
  • name (string) – custom type name
  • base_type (string) – base type
  • allow_arrays (boolean) – custom type may be returned as an array using the standard [] notation
  • macro (boolean) – If True all functions using this type will automatically be registered as a macro sheet equivalent function
  • thread_safe (boolean) – If False any function using this type will never be registered as thread safe