VBA运行时错误80010108：Name对象的RefersToRange方法调用失败

问题复现 #

• 通过单单元格命名区域RowMarker跟踪行的添加/删除操作，逻辑在Worksheet_Change事件中执行
• 新增行时，B列自动生成数据验证下拉列表，选项取自其他工作表的表格
• 选择B列选项后，为F列生成动态下拉列表时触发错误：

  Run-time error '-2147417848 (80010108)': Method 'RefersToRange' of object 'Name' failed
  

• 命名区域在名称管理器中可见，遍历工作表命名区域也能获取正确地址，但尝试指定工作表、ThisWorkbook前缀等引用方式，要么触发相同错误，要么导致Excel崩溃

错误根源 #

这个错误的核心原因是事件触发过程中Excel对象模型未完全更新：

1. Worksheet_Change事件执行时，Excel正在处理单元格的变更操作，命名区域的内存引用状态不稳定，导致RefersToRange无法正确解析
2. 若RowMarker使用相对地址，插入新行后引用上下文发生偏移，进一步加剧了对象解析失败
3. 未禁用事件重入，导致嵌套触发的事件干扰了当前操作的对象状态

解决方案 #

1. 禁用事件重入 #

在事件开头禁用Excel事件触发，避免嵌套执行导致的对象状态混乱，结尾必须恢复事件状态：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo Cleanup ' 确保异常时也能恢复事件
    
    ' 核心代码逻辑
    
Cleanup:
    Application.EnableEvents = True
    Exit Sub
End Sub

2. 绕开RefersToRange方法，直接解析引用地址 #

不依赖RefersToRange获取区域，而是通过解析命名区域的RefersTo字符串来定位单元格：

' 获取RowMarker对应的单元格
Dim rowMarkerAddr As String
rowMarkerAddr = ThisWorkbook.Names("RowMarker").RefersTo
Set rngRowMarker = Me.Range(Mid(rowMarkerAddr, 2)) ' 去掉RefersTo开头的"="符号

3. 添加状态刷新延迟 #

在处理动态下拉列表前，调用DoEvents让Excel完成当前操作的UI和对象模型刷新：

If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
    DoEvents ' 等待Excel完成状态更新
    ' 生成F列动态下拉的代码
End If

4. 确保命名区域使用绝对地址 #

检查RowMarker的引用格式，必须使用绝对地址（如=Sheet1!$A$1），避免插入行后相对地址自动偏移导致的引用错误

修改后的完整示例代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngRowMarker As Range
    Dim dynamicListAddr As String
    
    Application.EnableEvents = False
    On Error GoTo Cleanup

    ' 处理行添加/删除时的B列下拉列表
    If Not Intersect(Target, Me.Range("RowMarker")) Is Nothing Then
        With Target.Offset(0, 1).Validation ' B列为RowMarker右侧1列
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="=Sheet2!Table1[类别]" ' 替换为实际的选项来源
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    End If

    ' 处理B列选择后的F列动态下拉
    If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
        DoEvents ' 等待Excel刷新状态
        
        ' 解析RowMarker的区域
        Dim rowMarkerAddr As String
        rowMarkerAddr = ThisWorkbook.Names("RowMarker").RefersTo
        Set rngRowMarker = Me.Range(Mid(rowMarkerAddr, 2))
        
        ' 根据B列值生成动态列表地址（示例逻辑）
        dynamicListAddr = "=Sheet3!Table2[数据列]" ' 替换为实际的动态筛选逻辑
        
        With Target.Offset(0, 4).Validation ' F列为B列右侧4列
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:=dynamicListAddr
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    End If

Cleanup:
    If Err.Number <> 0 Then
        MsgBox "错误信息：" & Err.Description & vbCrLf & "错误代码：" & Err.Number
    End If
    Application.EnableEvents = True
    Exit Sub
End Sub

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