How to use Application.MaxChange in the xlwings API way

The Application.MaxChange property in Excel VBA is used to set or return the maximum amount by which cell values can change during an iterative calculation, such as when using circular references with the iteration feature enabled. In xlwings, this property can be accessed through the Application object, allowing Python scripts to control Excel’s iterative calculation settings programmatically. This is particularly useful for financial modeling, engineering simulations, or any scenario where iterative solutions are required, as it helps define convergence criteria to prevent infinite loops.

Functionality:
MaxChange determines the threshold for changes in cell values between iterations. When Excel performs iterative calculations, it continues recalculating until either the maximum iterations limit is reached or the change in all cell values is less than both MaxChange and MaxIterations settings. By adjusting MaxChange, you can fine-tune the precision of iterative results, balancing accuracy with calculation speed.

Syntax in xlwings:
In xlwings, the MaxChange property is accessed via the Application object. The syntax is straightforward:

app = xw.App() # Connect to an Excel instance
max_change_value = app.api.MaxChange # Get the current MaxChange value
app.api.MaxChange = new_value # Set a new MaxChange value

Here, app is an xlwings App object representing the Excel application. The .api attribute provides access to the underlying Excel object model, allowing direct interaction with properties like MaxChange. The property accepts and returns a float value, representing the maximum change tolerance. For example, setting it to 0.001 means iterations will stop when cell value changes are less than 0.001.

Parameters and Values:

• Value Type: Float (e.g., 0.001, 0.0001). It must be a positive number; setting it to 0 or negative may cause errors or unexpected behavior.
• Default Value: In Excel, the default is 0.001, but this can vary based on user settings or workbook configurations.
• Interaction with Other Settings: MaxChange works in conjunction with MaxIterations (accessible via app.api.MaxIterations). Iterations stop when either the maximum number of iterations is reached or the change in values is below MaxChange.

Code Examples:
Below are practical xlwings API examples demonstrating how to use MaxChange in Python scripts:

1. Retrieving the Current MaxChange Value:
   This example connects to an active Excel instance and prints the current MaxChange setting.

import xlwings as xw
# Connect to the active Excel application
app = xw.apps.active
current_max_change = app.api.MaxChange
print(f"Current MaxChange value: {current_max_change}")

2. Setting MaxChange for Iterative Calculations:
   Here, we set MaxChange to a more precise value and enable iterative calculations by adjusting related properties.

import xlwings as xw
app = xw.App() # Start a new Excel instance
# Configure iterative calculation settings
app.api.Iteration = True # Enable iteration
app.api.MaxIterations = 100 # Set maximum iterations
app.api.MaxChange = 0.0001 # Set tighter change tolerance
print("MaxChange updated to 0.0001 for higher precision.")
# Open a workbook and perform calculations (e.g., with circular references)
wb = app.books.add()
ws = wb.sheets[0]
ws.range("A1").formula = "=B1+1" # Example circular reference setup
ws.range("B1").formula = "=A1*0.5"
wb.save("iterative_example.xlsx")
app.quit()

3. Resetting MaxChange to Default:
   This script resets MaxChange to Excel’s typical default value.

import xlwings as xw
app = xw.apps.active
app.api.MaxChange = 0.001
print("MaxChange reset to default 0.001.")