VBA生成工作簿定义名称表格时遇Run-time error 1004求助

问题原因 #

_xlfn.ANCHORARRAY是Excel的内置函数兼容名称，这类名称并非指向单元格区域的用户定义名称，而是Excel为兼容旧版本标记的新函数标识。它们没有RefersToRange属性，直接访问该属性就会触发"应用程序或对象定义的错误"。

解决方案 #

修改代码，添加过滤逻辑跳过内置函数名称，同时增加错误捕获处理其他非区域引用的名称（比如常量名称、公式名称）：

Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
Dim r As Integer
r = 1

For Each nm In nms
    ' 跳过Excel内置的函数（_xlfn.前缀）和命令（_xlc.前缀）名称
    If Left(nm.Name, 5) <> "_xlfn" And Left(nm.Name, 4) <> "_xlc" Then
        wks.Cells(r, 2).Value = nm.Name
        ' 错误捕获：处理无法获取区域引用的情况
        On Error Resume Next
        wks.Cells(r, 3).Value = nm.RefersToRange.Address
        If Err.Number <> 0 Then
            wks.Cells(r, 3).Value = "非区域引用：" & nm.RefersTo
            Err.Clear
        End If
        On Error GoTo 0
        r = r + 1
    End If
Next nm

代码说明 #

• 前缀过滤：通过判断名称前缀，精准跳过Excel内置的非用户定义名称，只保留自定义名称。
• 错误捕获：针对无法获取RefersToRange的名称，输出其RefersTo属性值，明确该名称的实际引用内容（比如常量、公式）。
• 遍历优化：改用For Each遍历Names集合，比按索引遍历更安全，避免因集合元素变动导致的索引偏移问题。

内容的提问来源于stack exchange，提问作者gianniC