_xlfn.ANCHORARRAY added to .to_com().Names if a formula contains #

[ShieldTrade]

Hi Cunnane,

Just to let you know. If you use # in a formula excel will add a _xlfn.ANCHORARRAY to the names xloil retrieves using
names = xlo.active_workbook().to_com().Names . The name does not appear in the xl Name manager but might cause some problem

Microsoft® Excel® 2021 MSO (Version 2410 Build 16.0.18129.20158) 64-bit
A1 = " Ohio". Named Range "aa"
A2 = 1. Named Range "BB"
A3 = xloil_list()
A4 = A3#. This will cause the problem
A5 = get_named_range_data()

import xloil as xlo
import pandas as pd
import string
wb = xlo.active_workbook()

@xlo.func()
def get_named_range_data() ->  pd.DataFrame:
    names = wb.to_com().Names # _xlfn.ANCHORARRAY if using #. Persist until reopening excel
    data = {'name': [], 'address': [],
            # 'value' : [], # uncomment for error
            'ws':[],'row':[],'col_l':[],'col_n':[] }
    for named_range in names:
        address = named_range.RefersTo[1:]
        data['name'].append(named_range.Name)
        data['address'].append(address)
        # data['value'].append(wb[address].value) # uncomment RuntimeError: COM Error 0x800a03ec: 
        data['ws'].append(address.partition('!')[0])
        data['row'].append(address.partition('$')[2].partition('$')[2])
        data['col_l'].append(address.partition('$')[2].partition('$')[0])
        data['col_n'].append(col_to_num(address.partition('$')[2].partition('$')[0]))
    df = pd.DataFrame(data)
    return df.set_index('name')

 def col_to_num(col_l):
    num = 0
    for c in col_l:
        if c in string.ascii_letters:
            num = num * 26 + (ord(c.upper()) - ord('A')) + 1
    return float(num)

@xlo.func()
def xloil_list():
    return[1,2,3]