Excel跨工作表定义Range时触发Error 1004问题咨询

问题成因 #

报错的核心原因是VBA中Range对象的隐式工作表绑定规则：

• 未显式指定Range所属的工作表时，VBA会默认将Range绑定到当前活动工作表，在Worksheet_Change事件中，活动工作表默认就是触发事件的工作表（即你场景中的Sheet_A）
• 原代码Set Rng = Range(Rng, Rng.offset(0,1))中的Range没有指定工作表，而此前定义的Rng属于Sheet_B，相当于尝试用Sheet_B的单元格地址在Sheet_A中构建Range，跨表的单元格边界匹配失败，就会抛出1004错误。
• 改写为Address版本后不报错，是因为Address返回的是纯文本地址，相当于在Sheet_A中构建了一个和Sheet_B的A1:B1地址相同的Range，这个Range实际属于Sheet_A，完全不是你需要的Sheet_B范围，所以后续调用Rng.Select会失败：Excel不允许选中非活动工作表的单元格。
• 在Sheet_B的事件中运行相同逻辑正常，是因为默认绑定的工作表就是Sheet_B，和Rng所属的工作表一致，不会出现边界匹配问题。

解决方案 #

所有跨表操作Range时，必须显式指定Range所属的父工作表，不要依赖隐式绑定规则：

正确代码示例 #

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 提前声明工作表对象，避免重复调用Sheets集合
    Dim wsB As Worksheet
    Set wsB = ThisWorkbook.Sheets("Sheet_B")
    
    Dim Rng As Range
    Set Rng = wsB.Range("A1")
    ' 显式用wsB调用Range，保证所有单元格都属于Sheet_B
    Set Rng = wsB.Range(Rng, Rng.Offset(0, 1))
    
    ' 如果要执行Select方法，必须先激活对应的工作表
    wsB.Activate
    Rng.Select
End Sub

额外注意事项 #

• 如果确认工作表存在保护，需要操作前先撤销保护，操作完成后再恢复：

  ' 无密码时Password参数留空即可
  wsB.Unprotect Password:="你的保护密码"
  ' 这里放置操作单元格的代码
  ' 操作完成后恢复保护
  wsB.Protect Password:="你的保护密码"
  

• 在Worksheet_Change事件里修改单元格内容时，建议先关闭事件触发，避免无限递归：

  Application.EnableEvents = False
  ' 你的操作代码
  Application.EnableEvents = True
  

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