VBA检查特定单元格区域中的更改 [英] VBA check changes in a particular cell range

问题描述

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

I need a function that checks if a particular range of cells in vba was changed:

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

此代码有效，问题是:它们更改为相同的值(引发了Internet更新).如何检查更新后的单元格值是否真的更改为新的值?(无需一一检查)

This code works, the problem is: they changed to the same values (throw an internet update). How to check if the updated cell values really changed to new ones? (without checking one by one ofc)

预先感谢

推荐答案

在这个小示例中，我们维护数据块的内存，因此可以测试是否实际更改了任何值.请注意，内存已在子控件上方变暗以使其变为静态:

In this small example, we maintain the memory of the block of data and can thus test if any value has actually changed. Note the memory has been Dim'ed above the sub to make it static:

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

这篇关于VBA检查特定单元格区域中的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！