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
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).
xl_app can be used to get the Excel.Application COM object
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 = xl_app()
xl.Selection.Value = value
@xl_macro("string n: int")
You can assign keyboard shortcuts to your macros by using the ‘shortcut’ keyword argument to the
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
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.
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.
py_strlen example above, to call that from VBA you would use the Run VBA function, e.g.
x = Run("py_strlen", "my string")