Excel VBA脚本计算异常：OldValues旧值未正确参与运算

核心问题分析 #

你的VBA脚本中OldValues未正确参与计算，根源是以下几个明确错误：

• 变量引用时机错误：在For Each CelL In Target循环前就调用CelL.Address，此时CelL未被赋值，根本取不到对应单元格的旧值
• 拼写错误：CelL.Adress少写了一个字母，应为CelL.Address，这个错误直接导致取值失败，加上On Error Resume Next的掩盖，让问题更隐蔽
• 集合初始化不严谨：Set OldValues = Nothing后没有重新创建新的Collection实例，后续Add操作会失效
• 变量作用域问题：ValInput、ValOffsetLong等变量定义在循环外，若Target涉及多单元格（虽代码限制了单个，但逻辑上需对应每个单元格）会导致取值错误

修正后的代码 #

Dim OldValues As Collection ' 改为普通声明，手动控制初始化时机

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 排除多区域、多单元格、非Range对象的无效选择
    If Selection.Areas.Count > 1 Or Target.Count > 1 Or TypeName(Target) <> "Range" Then Exit Sub
    
    ' 重新初始化集合，清空旧数据
    Set OldValues = New Collection
    Dim CelL As Range
    For Each CelL In Target
        ' 以单元格地址为唯一键，存储选中时的旧值
        OldValues.Add CelL.Value, Key:=CelL.Address
    Next CelL
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 同样排除无效选择场景
    If Selection.Areas.Count > 1 Or Target.Count > 1 Or TypeName(Target) <> "Range" Then Exit Sub
    
    Dim CelL As Range, ValInput As Long, ValOffsetLong As Long, ValOld As Long, ValNew As Long
    Dim ValOffsetString As String, r As Long, c As Long
    Dim keyCells As Range: Set keyCells = Range("C12:C19,C22:C29")
    
    ' 仅在合并依赖单元格时启用错误捕获
    On Error Resume Next
    Set keyCells = Application.Union(keyCells.Precedents, keyCells)
    On Error GoTo 0 ' 关闭错误捕获，避免掩盖后续问题
    
    Application.EnableEvents = False
    If Not Application.Intersect(Target, keyCells) Is Nothing Then
        ' 循环处理每个目标单元格，确保变量对应正确的单元格数据
        For Each CelL In Target
            r = CelL.Row
            c = CelL.Column
            ValInput = CelL.Value ' 取当前单元格的新输入值
            ValOffsetLong = CelL.Offset(163, 11).Value ' 明确取单元格的值而非Range对象
            ValOffsetString = CelL.Offset(163, 11).Address(0, 0)
            
            ' 从集合中读取对应单元格的旧值
            ValOld = OldValues(CelL.Address)
            ValNew = ValInput + ValOld - ValOffsetLong ' 执行正确的计算逻辑
            
            Debug.Print "/SelctedCell(Target): " & CelL.Address & " /Input(CelL.Value): " & CelL.Value _
              & " /ValOld: " & ValOld & " /ValOffsetLong: " & ValOffsetLong & " /ValNew: " & ValNew & " /ValOffsetString: " & ValOffsetString
            
            ' 向单元格写入目标公式
            Cells(r, c).Formula = "=" & ValNew & "+" & ValOffsetString
        Next CelL
    End If
    
    ' 更新集合为当前单元格的新值，供下次操作使用
    Set OldValues = New Collection
    For Each CelL In Target
        OldValues.Add CelL.Value, Key:=CelL.Address
    Next CelL
    
    Application.EnableEvents = True
End Sub

关键修改说明 #

1. 集合初始化优化：把自动实例化的Dim OldValues As New Collection改为普通声明，每次需要时手动Set OldValues = New Collection，避免自动实例化带来的不确定性
2. 修正变量引用逻辑：把ValOld的获取、ValInput等变量的赋值放到For Each CelL循环内部，确保每个单元格都对应正确的旧值和偏移值
3. 修复拼写错误：把CelL.Adress改为CelL.Address，确保能正确从集合中读取对应单元格的旧值
4. 明确取值操作：ValOffsetLong = CelL.Offset(163, 11).Value加上.Value，明确取单元格的数值而非Range对象
5. 错误捕获优化：仅在合并依赖单元格的步骤启用错误捕获，其余时间关闭，避免掩盖其他潜在问题

测试验证 #

按你给出的场景：单元格原有值1，偏移单元格N190值2，输入新值10后，ValNew = 10 + 1 - 2 = 9，最终单元格会写入公式=9+N190，完全符合预期逻辑。

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