Excel VBA Worksheet_Change事件Range的Formula方法报错

• 核心诱因是事件递归重入冲突：Worksheet_Change事件的触发条件是单元格内容被修改，你在事件过程内部给单元格赋值Formula的操作，会再次触发Change事件，形成嵌套调用。原代码中的公式带结构化表引用、XLOOKUP查找逻辑，执行耗时比简单公式长，嵌套触发时Range对象状态被打断，就会抛出Method 'Formula' of object 'Range' failed错误；简单公式执行速度快，时序上没撞上冲突所以不会报错。
• 引用对象错误：代码里用ActiveCell获取被修改的单元格是不稳定的，Change事件触发时Target才是真正被编辑的单元格，焦点变化、事件重入都会导致ActiveCell指向非预期单元格，引用错位也会触发赋值失败。
• 缺少事件状态保护：没有在修改单元格前临时禁用事件，也没有错误兜底逻辑，一旦代码运行异常，会导致Excel全局事件被关闭，后续所有单元格事件都不会触发。

按照以下逻辑调整代码即可解决问题：

1. 修改单元格前临时关闭事件触发，操作完成后再恢复，从根源避免递归重入
2. 所有被编辑单元格的引用统一用Target，替换不稳定的ActiveCell引用
3. 增加错误处理逻辑，哪怕代码运行出错，也要保证事件状态能被正常恢复，避免影响Excel其他功能

修复后的完整代码如下：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim targetArea As Range
    Set targetArea = Me.Range("P5:P100")
    
    ' 错误兜底，保证异常时可恢复事件
    On Error GoTo ErrCatch
    Application.EnableEvents = False
    
    ' 非单单元格编辑、不在目标区域直接退出
    If Target.Cells.Count > 1 Then GoTo SafeExit
    If Intersect(Target, targetArea) Is Nothing Then GoTo SafeExit
    
    ' 单元格被清空时恢复预置公式
    If Target.Value = "" Then
        Target.Formula = "=IF(N5=""+"",XLOOKUP(LEFT(O5,9),Table6[SKU],Table6[Costo Unitario (DOP)],""Revisar"",-1,-1),"""")"
    End If

SafeExit:
    ' 恢复全局事件状态
    Application.EnableEvents = True
    Exit Sub
ErrCatch:
    MsgBox "代码运行错误：" & Err.Description, vbExclamation
    Resume SafeExit
End Sub

补充排查点：如果上述调整后依然报错，请确认你使用的Excel版本支持XLOOKUP函数，该函数仅在Excel 2021、Microsoft 365及更高版本可用，低版本Excel需要将XLOOKUP替换为INDEX+MATCH组合实现相同查找逻辑。

内容的提问来源于stack exchange，提问作者Juan López