How to use Application.Evaluate in the xlwings API way

The Application.Evaluate method in Excel is a powerful tool for converting a Microsoft Excel name or a formula string into an actual value or a reference object. In xlwings, this functionality is exposed through the api property of an App or Book object, allowing you to leverage Excel’s calculation engine directly from Python. This is particularly useful for evaluating complex formulas that are easier to express in Excel’s native syntax than to replicate in Python code, or for retrieving the value of a defined name.

The syntax in xlwings follows the pattern of accessing the underlying Excel object model. To call Evaluate, you first obtain the Excel Application object via the api property. The method takes a single string argument, which is the name or formula to be evaluated.

Syntax:

app.api.Evaluate(Name)

• app: This is your xlwings App object (e.g., xlwings.App() or xw.apps.active).
• .api: This property provides direct access to the pywin32 or appscript COM object, enabling calls to the raw Excel VBA object model.
• .Evaluate: The method being called.
• Name (String, Required): A formula, a defined name, or a reference (as a string) that you want Excel to evaluate. This must be provided in the locale of the Excel application or in A1-style notation. For example, "SUM(A1:B10)", "MyNamedRange", or "Sheet1!$C$5".

The method returns the result of the evaluated formula. This can be a simple data type (like a number, string, or boolean), an xlwings.Range object (if the name evaluates to a range reference), or an error.

Code Examples:

1. Evaluating a Mathematical Formula:
   This calculates a formula string and returns the numeric result.

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.add()
# Place values in cells
wb.sheets[0].range('A1').value = 10
wb.sheets[0].range('A2').value = 20

# Use Evaluate to compute the sum
result = app.api.Evaluate("SUM(A1:A2)")
print(result) # Output: 30.0
wb.close()
app.quit()

2. Evaluating a Defined Name:
   This retrieves the value or reference associated with a name defined in the workbook.

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.add()
# Define a name for a range
wb.api.Names.Add(Name="MyData", RefersTo="=Sheet1!$A$1:$A$5")
# Put values into the named range
wb.sheets['Sheet1'].range('A1:A5').value = [[1], [2], [3], [4], [5]]

# Evaluate the defined name to get its value (the array)
name_result = app.api.Evaluate("MyData")
print(list(name_result)) # Output: [1.0, 2.0, 3.0, 4.0, 5.0]
wb.close()
app.quit()

3. Evaluating a Formula that Returns a Range Object:
   This is useful for obtaining an xlwings Range object from a string reference.

import xlwings as xw

app = xw.App(visible=True)
wb = app.books.add()
ws = wb.sheets[0]
ws.range('B2').value = "Hello World"

# Evaluate returns a Range COM object, which xlwings wraps.
# The xlwings Range constructor can handle this COM object.
range_ref = app.api.Evaluate("Sheet1!B2")
xl_range = xw.Range(range_ref) # Wrap it in xlwings Range
print(xl_range.value) # Output: Hello World
print(xl_range.address) # Output: $B$2
# wb.close() and app.quit() omitted for an interactive example.