Macro Functions¶

These decorators, functions and classes are used to expose Python functions to Excel as macro functions (Subs).

Please see Macro Functions for complete details on writing Excel macro function in Python.

@xl_macro¶

xl_macro([signature,] [allow_abort=None,] [name=None,] [shortcut=None,] [nan_value,] [posinf_value,] [neginf_value,] [none_value,] [disable_calculation=False,] [disable_screen_updating=False,] [disable_alerts=False,] [restore_selection=False,] [restore_active_sheet=False])

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).
transpose (boolean) – If true, if an array is returned it will be transposed before being returned to Excel.
nan_value –
Value to use in the case that the return value is NaN.

Defaults to the global setting nan_value set in the config file, or #NUM! if not set.

Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.

@Since PyXLL 5.5.0
posinf_value –
Value to use in the case that the return value is +Inf.

Defaults to the global setting posinf_value set in the config file, or Excel’s own numeric representation of +Inf if not set.

Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.

@Since PyXLL 5.5.0
neginf_value –
Value to use in the case that the return value is -Inf.

Defaults to the global setting neginf_value set in the config file, or Excel’s own numeric representation of -Inf if not set.

Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.

@Since PyXLL 5.5.0
none_value –
Value to use in the case that the return value is None.

Defaults to the global setting none_value set in the config file. If not set, the Excel NULL value is used which gets displayed as 0 by default.

@Since PyXLL 5.9.0
automatic_calculations –
Disable automatic calculations until finished.

@Since PyXLL 5.9.0
screen_updating –
Disable screen updating until finished.

@Since PyXLL 5.9.0
disable_alerts –
Disable alerts until finished.

@Since PyXLL 5.9.0
restore_selection –
Restore the current selection when finished.

@Since PyXLL 5.9.0
restore_active_sheet –
Restore the current active sheet when finished.

@Since PyXLL 5.9.0

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)

See Macro Functions for more details about using the xl_macro decorator.

xl_app¶

xl_app(com_package=None)

Gets the Excel Application COM object and returns it as a win32com.Dispach, comtypes.POINTER(IUknown), pythoncom.PyIUnknown or xlwings.App object, depending on which COM package is being used.

Many methods and properties Excel Application COM object will fail if called from outside of an Excel macro context. Generally, xl_app should only be used from Python code called from an Excel macro [1], menu [1], or worksheet function [1] [2]. To use it from any other context, or from a background thread, schedule a call using schedule_call.

Parameters:: com_package (string) – The Python package to use when returning the COM object. It should be None, ‘win32com’, ‘comtypes’, ‘pythoncom’ or ‘xlwings’. If None the com package set in the configuration file will be used, or ‘win32com’ if nothing is set.
Returns:: The Excel Application COM object using the requested COM package.

Warning

Excel COM objects should never be passed between threads. Only use a COM object in the same thread it was created in. Doing otherwise is likely to crash Excel! If you are using a background thread the safest thing to do is to only call into Excel using COM via functions scheduled using schedule_call.

xl_disable¶

New in PyXLL 5.9

xl_disable(automatic_calculations=True, screen_updating=True, alerts=True, restore_selection=True, restore_active_sheet=True)

Context manager to disable Excel while performing operations that interact with Excel.

Can only be used from inside an Excel macro, or from a function scheduled using pyxll.schedule_call.

Example:

@xl_macro
def macro_function():
    with xl_disable():
        # do some work here that updates Excel where we do not
        # want Excel to automatically recalculate or update.
        xl = xl_app()
        xl.Range("A1").Value = 1

    # After the with block, Excel reverts to its previous calculation mode.
    return

Parameters:

automatic_calculations – Disable automatic calculations until finished.
screen_updating – Disable screen updating until finished.
alerts – Disable alerts until finished.
restore_selection – Restore the current selection when finished.
restore_active_sheet – Restore the current active sheet when finished.

XLCell¶

class XLCell

XLCell represents the data and metadata for a cell (or range of cells) in Excel.

XLCell instances are passed as an xl_cell argument to a function registered with xl_func, or may be constructed using from_range.

Some of the properties of XLCell instances can only be accessed if the calling function has been registered as a macro sheet equivalent function [3].

Example usage:

from pyxll import xl_func

@xl_func("xl_cell cell: string", macro=True)
def xl_cell_test(cell):
    return "[value=%s, address=%s, formula=%s, note=%s]" % (
                cell.value,
                cell.address,
                cell.formula,
                cell.note)

from_range(range)

Static method to construct an XLCell from an Excel Range instance or address (e.g. ‘A1’).

The Range class is part of the Excel Object Model, and can be obtained via xl_app.

See Python as a VBA Replacement.

The XLCell instance returned can be used to get and set values in Excel using PyXLL type converters and object cache.

Parameters:: range – An Excel Range object or cell address as a string.

Example usage:

xl = xl_app()
range = xl.Selection
cell = XLCell.from_range(range)
cell.options(type='object').value = x