How to use Application.DeleteCustomList in the xlwings API way

The DeleteCustomList member of the Application object in Excel VBA is used to remove a previously defined custom autofill or sort list. In xlwings, which provides a Pythonic interface to Excel’s object model, this functionality can be accessed through the api property of an App or Book object, which exposes the underlying VBA object model. This is particularly useful for managing custom lists programmatically, such as cleaning up temporary lists or resetting configurations in automated Excel tasks.

Functionality
The primary purpose of DeleteCustomList is to delete a custom list that has been added to Excel. Custom lists are often used for custom sorting orders or to define autofill sequences (e.g., a list of department names or project stages). Deleting a list can help maintain a clean Excel environment, especially when lists are created dynamically during a script’s execution and are no longer needed afterward.

Syntax in xlwings
In xlwings, you call this method via the Application object obtained from an xlwings App instance. The syntax is:

app.api.DeleteCustomList(ListNum)

• app: This is an xlwings App object, representing the Excel application.
• api: This property provides direct access to the VBA Application object.
• DeleteCustomList: The method being called.
• ListNum: A required parameter of type Integer. It specifies the index number of the custom list to delete. The index corresponds to the position of the list in Excel’s custom lists collection, where custom lists are numbered sequentially starting from 1. Note that Excel’s built-in lists (like days and months) cannot be deleted and are not included in this count; the indexing applies only to user-defined custom lists.

To determine the correct ListNum for a specific list, you may need to retrieve it from Excel’s list collection. This can be done by using the GetCustomListNum method or by iterating through custom lists if you know the list’s contents. However, DeleteCustomList itself does not identify lists by name; it requires the numerical index.

Code Example
Below is an example demonstrating how to use DeleteCustomList in xlwings. This script adds a custom list, confirms its addition, and then deletes it. Note that error handling is important because attempting to delete a non-existent list or an out-of-range index will raise a com error.

import xlwings as xw

# Start or connect to Excel application
app = xw.App(visible=False) # Set visible=True to see Excel interface

try:
    # First, add a custom list for demonstration
    custom_list = ["North", "South", "East", "West"]
    app.api.AddCustomList(ListArray=custom_list)
    print("Custom list added successfully.")

    # Assume we want to delete the most recently added list.
    # In a real scenario, you might need to find the index dynamically.
    # Here, we use index 1, assuming it's the first user-defined list.
    # Note: This might fail if other custom lists exist.
    list_num = 1 # Index for the custom list to delete
    app.api.DeleteCustomList(ListNum=list_num)
    print(f"Custom list at index {list_num} deleted.")

except Exception as e:
    print(f"An error occurred: {e}")

finally:
    # Close Excel
    app.quit()

In this example, list_num is hard-coded as 1 for simplicity. In practice, to reliably delete a specific list, you might first use GetCustomListNum to find its index based on the list array, or maintain a record of list indices when creating them. The AddCustomList method returns the index of the newly created list, which can be stored for later deletion. For instance:

# When adding a list, store the returned index
new_list_index = app.api.AddCustomList(ListArray=custom_list)
# Later, delete using the stored index
app.api.DeleteCustomList(ListNum=new_list_index)