Assigning a macro with arguments to a button in Excel – PyXLL

It is possible to assign a macro written as a Python function to a button or other control in Excel by right clicking the control and selecting "Assign Macro" (see Calling macros from Excel)

For example,

from pyxll import xl_macro, xlcAlert

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

Assign the macro to a button:

But what about if your macro needs one or more arguments and you want to pass those from the button to the macro?

from pyxll import xl_macro, xlcAlert

@xl_macro
def my_macro(message):
    xlcAlert(message)

Passing Data to a Macro from a Button

Unfortunately it is not possible to pass arguments from the "Assign Macro" dialog for a button to a shared macro function like the ones written using PyXLL. Instead, we have to use another way to achieve what we need.

We can use the Excel Object Model to access the button that the macro was called by (see Using Python as a VBA replacement). Then, we can use properties on that button to determine how our macro behaves instead of passing an argument to the macro.

In the above example, instead of passing "message" as some text from the "Assign Macro" dialog we can get the button object that the macro was called from and show the button name:

from pyxll import xl_macro, xlcAlert, xl_app

@xl_macro
def my_macro():
    xl = xl_app()
    
    # Get the button object that called this macro
    button = xl.ActiveSheet.Shapes[xl.Caller]

    # Use the button name as the message
    xlcAlert(button.Name)

Using the button name isn't always convenient as we may want to pass some other information to the macro that is different from the button name. We might even have buttons with the same name that we want to use to call our macro with some different data.

Another option is to use the "AlternativeText" property of the button. You can set a value as the AlternativeText for a button via the "Format Control..." option on the button's context menu:

And in the macro we can retrieve that Alt Text as follows:

from pyxll import xl_macro, xlcAlert, xl_app

@xl_macro
def my_macro():
    xl = xl_app()

    # Get the button object that called this macro
    button = xl.ActiveSheet.Shapes[xl.Caller]

    # Use the button's alt text as the message
    xlcAlert(button.AlternativeText)

This achieves our aim of having a macro that can behave differently depending on what button was used to invoke it, avoiding the problem of not being able to pass arguments directly from the Assign Macro dialog.

An Alternative Solution

The "Assign Macro" dialog can be used to call VBA Subs with arguments. We can use this to call our macro indirectly by writing a VBA wrapper in a module in the workbook.

This involves a little bit of VBA code, which I generally prefer to avoid, but if the solution outlined above is not suitable this is another option.

To call a macro from VBA use the "Run" command. For example, to call a macro "my_macro" with a single argument we can use this.:

Sub VBA_Macro(msg As String)
    Run "my_macro", msg
End Sub

This VBA Sub can be assigned to a button using the syntax 'VBA_Macro "message"'.

Note: This syntax only works with VBA Subs and not with global or shared macros, which is why we need the VBA wrapper. To call the macro directly without VBA the solution outlined in the previous section can be used.

Passing Data to a Macro from a Button

An Alternative Solution

Related articles