Excel VBA中Worksheet的Range对象调用失败原因排查

错误原因 #

触发Run-time error '-2147417848'的核心问题在于：

• IsInsideColumn函数中使用ActiveCell判断单元格归属，而Worksheet_Change事件触发时，ActiveCell不一定是触发事件的Target单元格，易导致Intersect返回无效引用，引发对象操作错误。
• 未对表格对象、数据区域的有效性做校验，若表格结构变化（如无数据行），也会触发类似错误。

修正后的代码 #

1. 修改IsInsideColumn函数 #

替换ActiveCell为事件传入的Target，新增表格有效性校验：

Function IsInsideColumn(columnName As String, targetRng As Range) As Boolean
    Dim tbl As ListObject
    Dim colRange As Range
    
    Set tbl = Sheets(1).ListObjects("tblLIBERACOES")
    If tbl Is Nothing Then Exit Function
    
    On Error Resume Next
    Set colRange = tbl.ListColumns(columnName).DataBodyRange
    On Error GoTo 0
    
    If Not colRange Is Nothing Then
        If Not Intersect(targetRng, colRange) Is Nothing Then
            IsInsideColumn = True
        End If
    End If
    
    Set colRange = Nothing
    Set tbl = Nothing
End Function

2. 调整UpdateALERTS过程 #

将Target参数传递给IsInsideColumn：

Sub UpdateALERTS(fieldNAME As Range)
    If IsInsideColumn("REGISTRATION DATE", fieldNAME) Then
        EraseALERTS fieldNAME
    End If
End Sub

3. 优化EraseALERTS过程 #

新增表格及列对象的有效性校验：

Sub EraseALERTS(fieldNAME As Range)
    Dim tbl As ListObject
    Dim colYELLOW As ListColumn
    Dim targetCell As Range
    
    Set tbl = Sheets(1).ListObjects("tblLIBERACOES")
    If tbl Is Nothing Then Exit Function
    
    On Error Resume Next
    Set colYELLOW = tbl.ListColumns("YELLOW ALERT")
    On Error GoTo 0
    If colYELLOW Is Nothing Then Exit Function
    
    Set targetCell = Intersect(fieldNAME.EntireRow, colYELLOW.DataBodyRange)
    If Not targetCell Is Nothing Then
        Application.EnableEvents = False
        targetCell.Value = ""
        Application.EnableEvents = True
    End If
    
    Set targetCell = Nothing
    Set colYELLOW = Nothing
    Set tbl = Nothing
End Sub

4. 保留原Worksheet_Change事件 #

Private Sub Worksheet_Change(ByVal Target As Range)
    UpdateALERTS Target
End Sub

额外注意事项 #

• 确保表格tblLIBERACOES存在于Sheets(1)，且列名REGISTRATION DATE、YELLOW ALERT拼写完全一致（区分大小写）。
• 代码已适配表格无数据行的场景，避免触发额外错误。

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