VBA 检查特定单元格范围内的更改

Question

已关闭。此问题不符合 Stack Overflow guidelines 。目前不接受答案。

---

要求提供代码的问题必须表现出对所解决问题的最低限度的了解。包括尝试的解决方案、为什么它们不起作用以及预期结果。另请参阅:Stack Overflow question checklist

已关闭10 年前。

Improve this question

我需要一个函数来检查 vba 中特定范围的单元格是否已更改:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A9:J100")) Is Nothing Then
      MsgBox Target.Address
   End If
End Sub

这段代码有效，问题是:它们更改为相同的值(抛出互联网更新)。如何检查更新后的单元格值是否真的更改为新值？ (没有一一检查ofc)

提前致谢

Answer 1

在这个小示例中，我们维护数据 block 的内存，因此可以测试是否有任何值实际上发生了更改。请注意，子系统上方的内存已被调暗以使其静态:

Dim MemoryOfThingsPast() As Variant
Dim init As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Interesting As Range
    Dim I As Long, J As Long, K As Long
    Set Interesting = Range("A9:J100")
    If init = 0 Then
        MemoryOfThingsPast = Interesting
        init = 1
        Exit Sub
    End If
    If Intersect(Target, Interesting) Is Nothing Then Exit Sub
    K = 1
    For I = LBound(MemoryOfThingsPast, 1) To UBound(MemoryOfThingsPast, 1)
        For J = LBound(MemoryOfThingsPast, 2) To UBound(MemoryOfThingsPast, 2)
            v1 = Interesting(K)
            v2 = MemoryOfThingsPast(I, J)
            If v1 <> v2 Then
                MsgBox "Cell " & Interesting(K).Address & " has really changed"
            End If
            K = K + 1
        Next J
    Next I
    MemoryOfThingsPast = Interesting
End Sub