Macro Functions

You can write an Excel macro in python to do whatever you would previously have used VBA for. Macros work in a very similar way to worksheet functions. To register a function as a macro you use the xl_macro decorator.

Macros are useful as they can be called when GUI elements (buttons, checkboxes etc.) fire events. They can also be called from VBA.

Macro functions can call back into Excel using the Excel COM API (which is identical to the VBA Excel object model). The function xl_app can be used to get the Excel.Application COM object (using either win32com or comtypes), which is the COM object corresponding to the Application object in VBA.

Exposing Functions as Macros

Python functions to be exposed as macros are decorated with the xl_macro decorator imported from the pyxll module.

from pyxll import xl_macro, xl_app, xlcAlert

@xl_macro
def popup_messagebox():
    xlcAlert("Hello")

@xl_macro
def set_current_cell(value):
    xl = xl_app()
    xl.Selection.Value = value

@xl_macro("string n: int")
def py_strlen(n):
    return len(x)

Keyboard Shortcuts

You can assign keyboard shortcuts to your macros by using the ‘shortcut’ keyword argument to the xl_macro decorator, or by setting it in the SHORTCUTS section in the config.

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’.

from pyxll import xl_macro, xl_app

@xl_macro(shortcut="Alt+F3")
def macro_with_shortcut():
    xlcAlert("Alt+F3 pressed")

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

Calling Macros From Excel

Macros defined with PyXLL can be called from Excel the same way as any other Excel macros.

The most usual way is to assign a macro to a control. To do that, first add the Forms toolbox by going to the Tools Customize menu in Excel and check the Forms checkbox. This will present you with a panel of different controls which you can add to your worksheet. For the message box example above, add a button and then right click and select ‘Assign macro...’. Enter the name of your macro, in this case popup_messagebox. Now when you click that button the macro will be called.

../_images/macro.png

It is also possible to call your macros from VBA. While PyXLL may be used to reduce the need for VBA in your projects, sometimes it is helpful to be able to call python functions from VBA.

For the py_strlen example above, to call that from VBA you would use the Run VBA function, e.g.

Sub SomeVBASubroutine
    x = Run("py_strlen", "my string")
End Sub