How to use Application.Run in the xlwings API way

The Application.Run method in Excel’s object model is a powerful tool for executing procedures, such as macros or user-defined functions, that are stored in Excel workbooks. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This allows Python scripts to interact with Excel in a manner similar to VBA, enabling the automation of complex tasks and the integration of custom VBA code with Python workflows.

Functionality:
The primary purpose of Application.Run is to run a specified macro or function. This can include macros defined in VBA modules, functions in add-ins, or procedures in other open workbooks. It is particularly useful for scenarios where you need to trigger existing VBA code from an external Python script, leveraging the strengths of both environments. For instance, you might use Python for data processing and analysis, then call a VBA macro to format the results or generate a specific report layout that is already built in Excel.

Syntax in xlwings:
In xlwings, you access this method via the Application object obtained from a workbook or app instance. The basic syntax is:

app.api.Run(Macro, Arg1, Arg2, ..., Arg30)

Where:

• app: This is the xlwings App object (e.g., xw.App() or xw.apps.active).
• Macro: A required string argument specifying the name of the macro or function to run. The name should be in the format "WorkbookName!MacroName" or "MacroName" if the macro is in the current workbook. For add-ins, you might use the add-in’s registered name.
• Arg1, Arg2, ..., Arg30: Optional arguments that can be passed to the macro. You can provide up to 30 arguments, which correspond to the parameters expected by the VBA procedure. These arguments can be of various data types, such as strings, numbers, or arrays, and they are passed by value to the macro.

Example Usage:
Suppose you have an Excel workbook named Report.xlsm with a VBA macro named FormatData that takes two arguments: a range address as a string and a boolean for enabling headers. You can call this macro from Python using xlwings as follows:

import xlwings as xw

# Connect to the open instance of Excel or start a new one
app = xw.apps.active # Assumes Excel is already open with the workbook

# Specify the macro name with workbook reference
macro_name = "Report.xlsm!FormatData"

# Define arguments: range address and header flag
range_address = "A1:D100"
headers_enabled = True

# Run the macro with arguments
app.api.Run(macro_name, range_address, headers_enabled)

# Alternatively, if the macro is in the active workbook, you can use:
# app.api.Run("FormatData", range_address, headers_enabled)