Writing Excel macros¶
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.
Exposing functions as macros¶
Python functions to be exposed as macros are decorated with the xl_macro decorator imported from the pyxll module.
- pyxll.xl_macro([signature=""] [, allow_abort=?])¶
xl_macro is a function that returns a decorator for exposing python functions to Excel as macros.
param string signature: 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 supplied, it is assumed the function takes no arguments and the return value is not used. Parameters: 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 Config file).
from pyxll import xl_macro import win32api @xl_macro() def popup_messagebox(): """pops up a message box""" win32api.MessageBox(0, "Hello", "Hello") @xl_macro("string x: int") def py_strlen(n): """returns the length of x""" return len(x)
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.
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
On their own macros might seem a bit limited. When you consider you can call back into Excel from macros using the Excel COM API to do everything you previously would have done in VBA they suddenly become a lot more useful.
There are more examples of macros called from controls in the examples supplied with PyXLL.