How to use Application.OnRepeat in the xlwings API way

The Application.OnRepeat property in Excel VBA is used to assign a macro name (as a string) that will be executed when a user clicks the “Repeat” command. This command is typically available after performing an action that can be repeated, such as via the toolbar or the shortcut Ctrl+Y. In the context of xlwings, which provides a programmatic bridge between Python and Excel’s object model, this property can be accessed and manipulated to automate repetitive tasks within an Excel session. However, it’s important to note that xlwings primarily interacts with Excel through its API, and while it can execute VBA code, directly setting the OnRepeat property is not a standard, out-of-the-box feature because it is deeply tied to the VBA environment and the user interface. Typically, xlwings focuses on data manipulation, analysis, and automation using Python, leaving UI-specific commands like OnRepeat to be handled within VBA if necessary.

Functionally, OnRepeat allows for the customization of what action is repeated, enabling developers to define a specific macro for repetition, which can enhance user efficiency. In xlwings, to work with this property, you would need to use the api property of an xlwings App or Book object to access the underlying Excel Application object from the COM interface. This gives you direct access to VBA properties and methods, including OnRepeat.

The syntax for accessing the OnRepeat property via xlwings is through the Application object’s COM interface. Here’s the general format:

import xlwings as xw

# Connect to the active Excel instance or create a new one
app = xw.apps.active

# Access the OnRepeat property
repeat_macro = app.api.OnRepeat # Get the current value
app.api.OnRepeat = "MacroName" # Set the value to a macro name

• app: This is an xlwings App object representing the Excel application.
• api: This property provides access to the pywin32 COM object, which mirrors the Excel Application object from the VBA object model.
• OnRepeat: This property can be get or set. When getting, it returns a string representing the name of the macro assigned to the Repeat command. When setting, it assigns a macro name (as a string) to the Repeat command. The macro must exist in the workbook’s VBA project.
• MacroName: A string specifying the name of the macro to be repeated. This macro should be defined in a VBA module within the workbook.

Note: The OnRepeat property is specific to the Excel Application session and may not persist after closing Excel. Also, it requires that macros are enabled in Excel, and the macro must be accessible (e.g., in a standard module).

Here is an example code instance using xlwings to set and use the OnRepeat property:

import xlwings as xw

# Assume Excel is open with a workbook containing a macro named 'RepeatFormatting'
wb = xw.books.active
app = wb.app

# Set the OnRepeat property to 'RepeatFormatting'
app.api.OnRepeat = "RepeatFormatting"

# To demonstrate, we can run the macro once via xlwings (if needed)
# First, ensure the macro is in a module. We can run it using the Run method:
app.api.Run("RepeatFormatting")

# Later, when the user clicks Repeat or presses Ctrl+Y, the 'RepeatFormatting' macro will execute again.

# To retrieve the current OnRepeat setting:
current_repeat = app.api.OnRepeat
print(f"The current Repeat macro is: {current_repeat}")

# To clear the OnRepeat property (set it to an empty string):
app.api.OnRepeat = ""