捕获单元格区域旧值报错Error 5：仅全选单元格生效的问题咨询

问题根源 #

当复制多单元格内容粘贴到单个选中单元格时，Excel会自动扩展目标区域以匹配复制区域的大小，此时Worksheet_Change的Target是多个单元格。但之前触发Worksheet_SelectionChange时，仅选中了单个单元格，OldValues集合里只存储了该单元格的旧值。遍历Target中的其他单元格时，它们的地址不在OldValues中，调用OldValues(rngCell.Address)就会抛出「Error 5 - 无效的过程调用或参数」错误。

修复代码 #

1. 修正旧值存储逻辑 #

Dim OldValues As Collection ' 不在声明时初始化，避免重复创建

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set OldValues = New Collection ' 每次选中变化时重新创建干净的集合
    Dim rngOld As Range
    For Each rngOld In Target
        ' 避免重复添加相同地址（防止异常场景）
        On Error Resume Next
        OldValues.Add rngOld.Value, rngOld.Address
        On Error GoTo 0
    Next rngOld
End Sub

2. 修改变更日志记录逻辑，添加错误处理 #

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    Dim sheetName As String
    sheetName = ActiveSheet.Name
    Dim strOld As String

    For Each rngCell In Target
        ' 检查当前单元格的旧值是否已记录
        On Error Resume Next
        strOld = OldValues(rngCell.Address)
        If Err.Number <> 0 Then
            strOld = "批量粘贴填充（无原始值记录）"
            Err.Clear
        End If
        On Error GoTo 0
        
        addToLog rngCell, strOld, sheetName
        strOld = ""
    Next rngCell
End Sub

3. 保留原日志添加代码（无需修改） #

Sub addToLog(rngCell As Range, strOld, sheetName As String)
    ActiveWorkbook.Sheets("Log").Protect Password:="Password", UserInterFaceOnly:=True
    With ActiveWorkbook.Sheets("Log")
        Dim intNextRow As Integer
        intNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

        .Range("A" & intNextRow).Value = rngCell.Address
        .Range("B" & intNextRow).Value = rngCell.Value
        .Range("C" & intNextRow).Value = strOld
        .Range("D" & intNextRow).Value = Environ("username")
        .Range("E" & intNextRow).Value = sheetName
        .Range("F" & intNextRow).Value = Now()
        
    End With
End Sub

改动说明 #

• 调整OldValues的初始化方式：避免声明时自动创建集合导致的残留数据问题，每次选中变化时重新实例化集合。
• 添加错误捕获逻辑：针对批量粘贴扩展出的无旧值记录的单元格，用说明文本替代空值，避免报错。
• 增加重复键防护：在存储旧值时处理异常，防止同一地址被重复添加到集合中。

注：如果需要完整记录所有批量粘贴单元格的原始旧值，基于SelectionChange的方式存在局限（未被选中的单元格无法提前记录旧值）。若需更完整的记录，可考虑在Worksheet_SelectionChange中存储整个工作表的单元格快照，但会带来一定性能开销，需根据实际需求权衡。

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