How to use Range.ApplyNames in the xlwings API way

The ApplyNames method in Excel’s object model is a powerful feature for applying a list of defined names to formulas within a specified range, replacing cell references with their corresponding named ranges where applicable. This enhances formula readability and maintainability. In xlwings, this functionality is accessed through the api property of a Range object, providing direct access to the underlying Excel VBA object model.

Functionality:
The primary purpose of ApplyNames is to scan formulas in a given range and substitute direct cell references (like A1 or $B$2) with defined names that refer to those cells. For example, if you have a named range “SalesData” referring to B2:B10, a formula like =SUM(B2:B10) within the specified range can be automatically converted to =SUM(SalesData). This method only applies names that are defined in the workbook.

Syntax in xlwings:
The xlwings API call mirrors the VBA syntax closely. The method is called on a Range object via its api property.

range_object.api.ApplyNames(Names, IgnoreRelativeAbsolute, UseRowColumnNames, OmitColumn, OmitRow, Order, AppendLast)

Parameters:

• Names: An optional variant. An array of defined names to apply. If omitted, all names in the workbook are applied.
• IgnoreRelativeAbsolute: An optional variant. If True, the method ignores the relative or absolute status of names and references. Default is True.
• UseRowColumnNames: An optional variant. If True, the method uses the row and column names of the range if no defined name exists. Default is True.
• OmitColumn: An optional variant. If True, replaces the entire reference with the column name if the row portion of the reference matches the named range. Default is False.
• OmitRow: An optional variant. If True, replaces the entire reference with the row name if the column portion of the reference matches the named range. Default is False.
• Order: An optional variant. Determines the order for evaluating row and column names. Use xlRowThenColumn (1) or xlColumnThenRow (2).
• AppendLast: An optional variant. If True, the row and column names are appended to the existing defined name. Default is False.

Code Example:
The following xlwings script demonstrates using ApplyNames. It assumes an active workbook with defined names and formulas in a specific range.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active

# Define a target range, e.g., from A1 to C10 on the first sheet
target_range = wb.sheets[0].range("A1:C10")

# Apply all defined names in the workbook to formulas in the target range.
# We use default parameters for IgnoreRelativeAbsolute and UseRowColumnNames.
target_range.api.ApplyNames()

# To apply only specific names, provide an array.
# For instance, applying only the names "Revenue" and "Costs":
target_range.api.ApplyNames(Names=["Revenue", "Costs"])

# Example with more parameters: Apply names, ignoring relative/absolute status,
# not using row/column names, and evaluating column names before row names.
target_range.api.ApplyNames(IgnoreRelativeAbsolute=True,
UseRowColumnNames=False,
Order=2) # 2 corresponds to xlColumnThenRow