How to use Application.ConvertFormula in the xlwings API way

The Application.ConvertFormula method in Excel is a powerful tool for transforming formula references between different reference styles, such as converting between A1 and R1C1 notation, or between relative, absolute, and mixed references. In xlwings, this functionality is exposed through the api property, allowing Python scripts to leverage Excel’s native conversion capabilities programmatically. This is particularly useful when generating or modifying formulas dynamically, ensuring compatibility across different workbook settings or user preferences.

Functionality
The primary purpose of ConvertFormula is to change the reference style of a formula. It can convert a formula string from the A1 reference style to R1C1, or vice versa. Additionally, it can modify the reference type—converting relative references (like A1) to absolute ($A$1), mixed (A$1 or $A1), or back. This is essential for tasks like template generation, where formulas need to be adjusted based on cell positions, or for macros that interact with formulas in a style-agnostic manner.

Syntax in xlwings
In xlwings, you access this method via the api property of the App or Book objects. The full syntax is:

app.api.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

The parameters are as follows:

• Formula (string): The formula string to be converted. This should be provided as text, without a leading equals sign.
• FromReferenceStyle (int): The reference style of the input formula. Use xlA1 (or 1) for A1 style, and xlR1C1 (or -4150) for R1C1 style.
• ToReferenceStyle (int): The desired reference style for the output. Same options as FromReferenceStyle.
• ToAbsolute (int): Specifies the type of absolute reference conversion. This parameter is optional and defaults to xlAbsolute (or 1). The common values are:
• xlAbsolute (1): Converts to absolute references.
• xlRelRowAbsColumn (2): Converts to mixed references with relative row and absolute column (e.g., A$1 becomes A1 in relative terms).
• xlAbsRowRelColumn (3): Converts to mixed references with absolute row and relative column (e.g., $A1 becomes A1 in relative terms).
• xlRelative (4): Converts to relative references.
• RelativeTo (object): A Range object that specifies the starting cell for relative references. This is required if ToAbsolute is set to xlRelRowAbsColumn, xlAbsRowRelColumn, or xlRelative. It defines the context for relative conversions.

Code Examples

1. Converting from A1 to R1C1 style:

import xlwings as xw
app = xw.App(visible=False)
# Convert the formula "SUM(A1:B2)" from A1 to R1C1 style
result = app.api.ConvertFormula("SUM(A1:B2)", 1, -4150)
print(result) # Output: SUM(R1C1:R2C2)
app.quit()

2. Changing relative references to absolute:

import xlwings as xw
app = xw.App(visible=False)
# Convert "A1+B2" to absolute references in A1 style
result = app.api.ConvertFormula("A1+B2", 1, 1, 1)
print(result) # Output: $A$1+$B$2
app.quit()

3. Using relative conversion with a specific cell context:

import xlwings as xw
app = xw.App(visible=False)
book = app.books.add()
sheet = book.sheets[0]
# Define the relative starting cell as C3
relative_cell = sheet.range("C3").api
# Convert "A1" to a relative reference based on C3
result = app.api.ConvertFormula("A1", 1, 1, 4, relative_cell)
print(result) # Output: This will be a relative formula like "RC[-2]" in R1C1, but in A1 style, it adjusts accordingly.
book.close()
app.quit()