VBA 检查特定单元格范围内的更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19088467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA check changes in a particular cell range
提问by Orange
I need a function that checks if a particular range of cells in vba was changed:
我需要一个函数来检查 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
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)
此代码有效，问题是：它们更改为相同的值（抛出 Internet 更新）。如何检查更新的单元格值是否真的更改为新的值？（不一一检查ofc）
Thanks in advance
提前致谢
回答by Gary's Student
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:
在这个小例子中，我们维护数据块的内存，因此可以测试任何值是否实际发生了变化。请注意，内存已在 sub 上方变暗以使其静态：
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
