How to use Range.ListNames in the xlwings API way

The ListNames property of the Range object in Excel’s object model is accessible through the xlwings API, providing a powerful way to retrieve a collection of all defined names that intersect with a specified range. This is particularly useful for auditing, data validation, or dynamically interacting with named ranges within a workbook. In xlwings, this property is exposed as a method on a Range object, returning a list of Name objects, each representing a defined name in the workbook.

Functionality:
The primary function is to list all defined names (created via “Formulas” > “Define Name” or through VBA) whose ranges have at least one cell in common with the specified Range object. If the specified range does not intersect with any defined names, an empty list is returned. This allows for targeted inspection of named ranges within specific worksheet areas.

Syntax:
In xlwings, the syntax is:

range_object.api.ListNames

Here, range_object is an xlwings Range instance (e.g., sheet.range('A1:D10')). The .api attribute provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS). The ListNames property does not take any parameters. It returns a pywin32 Names collection object, which can be iterated over or converted to a list. Each item in this collection is a pywin32 Name object, corresponding to a defined name in Excel.

Key Points:

• The property is read-only.
• It returns names defined at both the workbook and worksheet scope, but only if they intersect with the range.
• The intersection is based on the actual range referenced by the name, not just its starting cell.

Example:
Consider an Excel workbook where the following names are defined:

• SalesData referring to Sheet1!$A$1:$F$100
• Quarter1 referring to Sheet1!$B$1:$B$25
• Summary referring to Sheet2!$A$1:$A$10

The following xlwings code demonstrates how to use ListNames:

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
sheet = wb.sheets['Sheet1']

# Define a range on Sheet1
target_range = sheet.range('A1:C30')

# Get the list of names intersecting with this range
names_collection = target_range.api.ListNames

# Check if any names were found
if names_collection:
    print("Names intersecting with range A1:C30:")
    for name_obj in names_collection:
        # Access properties of the Name object
        name = name_obj.Name # The name string (e.g., "SalesData")
        refers_to = name_obj.RefersTo # The range reference (e.g., "=Sheet1!$A$1:$F$100")
        print(f" - {name}: {refers_to}")
else:
    print("No defined names intersect with the specified range.")