How to create a plot relative to a button in Excel using a Python macro?

When using the pyxll.plot function to plot a Python chart in Excel from a macro (see https://www.pyxll.com/docs/userguide/plotting/index.html), the initial position of the plot will be the currently selected cell.

It's common to assign a macro to a button in Excel so the user can run the macro by pressing that button, and you may want to create a plot in Excel with an initial position relative to that button.

The pyxll.plot function takes keyword arguments top, left, width and height, so you can set the initial position of the plot when calling it. In order to position it relative to the button that invoked the macro, you just need to find the position of that button.

This can be done using the Application.Caller property from the Excel API (see https://www.pyxll.com/docs/userguide/vba.html).

The following code shows how to get the button using the Application.Caller property, how to get it's position, and how to plot a chart relative to the button's position.

from pyxll import xl_app, xl_macro, plot

@xl_macro
def create_plot():
    # Get the Excel.Application instance
    xl = xl_app()

    # Get the caller of the macro. If this is a button
    # or another shape, this will be the ID as a string.
    caller = xl.Caller

    # Get the shape object from the active sheet
    shape = xl.ActiveSheet.Shapes[caller]

    # Create the figure to plot
    fig = ... 

    #
    # Plot the figure with it's initial position below
    # the button.
    #
    # Using a name means the same plot will be used if
    # the macro is called again, and its position and size
    # will not be reset.
    #
    # If you want the position and size to be reset each
    # time this is called then pass 'reset=True' to plot.
    #
    plot(fig,
         top = shape.Top + shape.Height,
         left = shape.Left,
         name = f"plot_{caller}")