在 VBA 中运行宏之前检查工作表是否已更新

Check if the worksheet is updated before running the macro in VBA

提问by hardikudeshi

I am writing a macro where there is a central input sheet - let us call this sheet - "Main Input sheet" where user inputs the variables concerned. In the "Main Input sheet" there are some inputs say - "Any More Input sheets?" - which when "Yes", a worksheet corresponding to the input is displayed (it was previously hidden) - Lets call it "Associated Input sheet". Now, I want to ensure that the user updates "Associated Input sheet" before he runs the macro. Is there a way I can do this - using event handlers that VBA provides or using any other way?

我正在编写一个宏，其中有一个中央输入表 - 让我们称这个表 - “主输入表”，用户输入相关变量。在“主输入表”中有一些输入说 - “还有更多输入表吗？” - 当“是”时，显示与输入对应的工作表（以前隐藏） - 让我们称之为“关联输入表”。现在，我想确保用户在运行宏之前更新“关联输入表”。有没有办法做到这一点 - 使用 VBA 提供的事件处理程序或使用任何其他方式？

采纳答案by Ben Strombeck

There is a Worksheet_change event that would probably do what you want:

有一个 Worksheet_change 事件可能会做你想做的事：

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Place this in the code for the "Main Info Sheet" and it will run everytime the sheet is changed.

将它放在“主信息表”的代码中，它会在每次更改表时运行。

However, if you don't want the spreadsheet to run every single time the sheet is updated, but only want to check if it has been updated... what you can do is create a global variable like this (the declaration must be placed in a standard module:

但是，如果您不希望电子表格在每次更新工作表时都运行，而只想检查它是否已更新......您可以做的是创建一个这样的全局变量（声明必须放在在标准模块中：

Global MainSheetHasChanged as Boolean

Then you would simply put this line of code in the worksheet_changed macro:

然后你只需将这行代码放在 worksheet_changed 宏中：

Private Sub Worksheet_Change(ByVal Target As Range)
    MainSheetHasChanged = True
End Sub

Just make sure to always set the variable back to false after running your other macro. Is this what you're looking for?

只需确保在运行其他宏后始终将变量设置回 false。这是你要找的吗？

回答by Nigel Heffernan

The Worksheet_Change event procedure is probably the way to go, unless you've got other stuff happening elsewhere on the sheet that makes lots of changes.

Worksheet_Change 事件过程可能是要走的路，除非你在工作表的其他地方发生了其他事情，导致了很多变化。

At that point, the your question can be rephrased: 'Has my range changed since I checked last?'

那时，您的问题可以改写为：“自上次检查以来，我的范围是否发生了变化？”

Grabbing a copy of the range and storing it somewhere, and checking the current range against the cached copy, cell-by-cell, is a brute force approach: it's OK if you're only doing it once, but if you're doing it repeatedly it's more efficient to store a hash - a short code or number generated by some kind of checksum function.

获取范围的副本并将其存储在某处，然后根据缓存的副本逐个单元地检查当前范围是一种蛮力方法：如果您只执行一次就可以，但是如果您正在执行反复存储散列更有效 - 由某种校验和函数生成的短代码或数字。

Checksum algorithms vary. Adler32 is simple and quick, but it performs badly - you get 'Hash Collisions' or failures to return differing hashes for data inputs that differ - on comparisons of (say) a pair of single words of 6-10 letters. However, it performs very well indeed when asked to detect changes to a column of 24 8-letter words, or to a table of a few thousand dates and numbers.

校验和算法各不相同。Adler32 既简单又快速，但它的性能很差 - 在比较（例如）一对 6-10 个字母的单个单词时，您会遇到“哈希冲突”或无法为不同的数据输入返回不同的哈希值。然而，当被要求检测对 24 个 8 字母单词的列或几千个日期和数字的表格的更改时，它确实表现得非常好。

Look up other hashes - and keep up-to-date: your PC will have several libraries with hashes like MD5 and sha1, which should perform better than a hand-rolled hash in VBA.

查找其他散列 - 并保持最新：您的 PC 将有几个带有散列的库，如 MD5 和 sha1，它们的性能应该比 VBA 中的手动散列更好。

Here's some demonstration code using the Adler-32 checksum. Read the code comments, there's stuff in there you'll need to know in order to adapt this to your project:

这是一些使用 Adler-32 校验和的演示代码。阅读代码注释，您需要了解其中的一些内容才能使其适应您的项目：

Public Function RangeHasChanged() As Boolean

' Demonstration function for use of the Checksum() function below.

' For more advanced users, I have a 'Watched Range' class on the website:
'   http://excellerando.blogspot.com

' Author: Nigel Heffernan, May 2006  http://excellerando.blogspot.com

' Please note that this code is in the public domain. Mark it clearly, with
' the author's name, and segregate it from any proprietary code if you need
' to assert ownership & commercial confidentiality on that proprietary code

' Coding Notes:

' It is expected that this function will be saved in the host worksheet's
' module and renamed to indicate the range or table being monitored. It's a
' good idea to use a named range rather than a hardcoded address.

' You might also choose to edit the '1 To 255' to the width of your range.

' Initialising the static values so that the first check in your VBA session
' does not automatically register a 'change' is left as an exercise for the
' reader: but calling the function on opening the workbook works well enough

' This is intended for use in VBA, not for use on the worksheet. Use the
' setting 'Option Private Module' to hide this from the function wizard.

Dim rngData As Excel.Range
Dim arrData As Variant

Dim lngChecksum As Long
Static lngExisting As Long

' Note that we capture the entire range in an Array, then work on the array:
' this is a single 'hit' to the sheet (the slow operation in any interaction
' with worksheet data) with all subsequent processing in VBA.

Set rngData = ThisWorkbook.Names("DataEntryMain").RefersToRange
arrData = rngData.Value2

RangeHasChanged = False

lngChecksum = CheckSum(arrData)

Erase arrData 

' lngExisting is zero when the file opens, and whenever the
' VBA project is reinitialised, clearing all the variables.
' Neither of these events should be reported as a 'change'.

if lngExisting <> lngChecksum AND lngExisting <> 0 Then
    RangeHasChanged = True
End If

lngExisting = lngChecksum

End Function

I could've sworn I posted this here, years ago, but here's an implementation of Adler-32 in 32-bit VBA.



There's a horrible hack in it: Adler-32 returns a 32-bit integer, and the VBA Long is a signed integer with a range ± (2^31) -1, so I've implemented a 'wrap around' of the overflow at +2^31, restarting at -2^31 +1. And done something I really, really shouldn't have done with a floating-point variable. Eventually everyone, everywhere, will have 64-bit Office and this'll be kind of quaint and unnecessary... Right?



Of course, the real question is: why bother?



It boils down to the common question of checking for changes: if you don't want to use the 'on change' event, or you're dealing with data directly in VBA before it hits the sheet, large data sets need something better than an item-by-item brute force approach. At least, if you're doing it more than once: the cost of rolling each item into your hash is always more than the cost of the one-by-one comparison...



...And that's still true if you're importing a fast hashing algorithm from MySQL or one of the web API libraries (try MDA5, if you can get at an exposed function), unless you can find something that reads VBA variant arrays directly and relieve your VBA thread of the task of enumerating the list values into the imported function.



Meanwhile, here's a hash algorithm that's within reach of VBA: Adler32. The details are in Wikipedia's article on Adler32: http://en.wikipedia.org/wiki/Adler-32  and an hour's testing will teach you some lessons about hashing:
'Hash collisions' (differing data sets returning the same hash code) are more common than you expected, especially with data containing repeated patterns (like dates);>
Choice of hashing algorithm is important;
...And that choice is more of an art than a science;
Admitting that you really shouldn't have bothered and resorting to brute force is often the better part of valour.



Adler-32 is actually more useful as a tool to teach those lessons, than as a workaday checksum. It's great for detecting changes in lists of more than 100 distinct items; it's tolerable, on a list of 24 randomly-generated 8-letter words (hash collisions at 1 in 1800 attempts) and it starts giving you single-digit percentage occurrences of the hash collision error in a list of 50 not-so-distinct option maturities, where the differences are mostly in the last 10 chars and those ten chars are recurring 3-month maturity dates. 



By the time you're comparing pairs of 6-letter strings, more than 10% of your changes will be missed by the checksum in a non-random data set. And then you realise that might as well be using string comparison for that kind of trivial computation anyway.



So the answer is always: test it.



Meanwhile, here's the algorithm, horrible hacks and all:




Public Function CheckSum(ByRef ColArray As Variant) As Long
Application.Volatile False

' Returns an Adler32 checksum of all the numeric and text values in a column

' Capture data from cells as myRange.Value2 and use a 32-bit checksum to see
' if any value in the range subsequently changes. You can run this on multi-
' column ranges, but it's MUCH faster to run this separately for each column
'
' Note that the VBA Long Integer data type is not a 32-bit integer, it's a
' signed integer with a range of  ± (2^31) -1. So our return value is signed
' and return values exceeding +2^31 -1 'wraparound' and restart at -2^31 +1.

' Coding Notes:

' This is intended for use in VBA, and not for use on the worksheet. Use the
' setting  'Option Private Module' to hide CheckSum from the function wizard

' Author: Nigel Heffernan, May 2006  http://excellerando.blogspot.com
' Acknowledgements and thanks to Paul Crowley, who recommended Adler-32

' Please note that this code is in the public domain. Mark it clearly, with
' the author's name, and segregate it from any proprietary code if you need
' to assert ownership & commercial confidentiality on your proprietary code

Const LONG_LIMIT As Long = (2 ^ 31) - 1
Const MOD_ADLER As Long = 65521

Dim a As Long
Dim b As Long

Dim i As Long
Dim j As Long
Dim k As Long

Dim arrByte() As Byte

Dim dblOverflow As Double

If TypeName(ColArray) = "Range" Then
    ColArray = ColArray.Value2
End If

If IsEmpty(ColArray) Then
    CheckSum = 0
    Exit Function
End If

If (VarType(ColArray) And vbArray) = 0 Then
    ' single-cell range, or a scalar data type
    ReDim arrData(0 To 0, 0 To 0)
    arrData(0, 0) = CStr(ColArray)
Else
    arrData = ColArray
End If

a = 1
b = 0

For j = LBound(arrData, 2) To UBound(arrData, 2)
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        
        ' VBA Strings are byte arrays: arrByte(n) is faster than Mid$(s, n)
        
        arrByte = CStr(arrData(i, j))  ' Is this type conversion efficient?
        
        For k = LBound(arrByte) To UBound(arrByte)
            a = (a + arrByte(k)) Mod MOD_ADLER
            b = (b + a) Mod MOD_ADLER
        Next k
        
        ' Terminating each item with a 'vTab' char constructs a better hash
        ' than vbNullString which, being equal to zero, adds no information
        ' to the hash and therefore permits the clash ABCD+EFGH = ABC+DEFGH
        ' However, we wish to avoid inefficient string concatenation, so we
        ' roll the terminating character's bytecode directly into the hash:
        
        a = (a + 11) Mod MOD_ADLER                ' vbVerticalTab = Chr(11)
        b = (b + a) Mod MOD_ADLER
        
    Next i
    
    ' Roll the column into the hash with a terminating horizontal tab char:
    
    a = (a + 9) Mod MOD_ADLER                     ' Horizontal Tab = Chr(9)
    b = (b + a) Mod MOD_ADLER

    
Next j

' Using a float in an integer calculation? We can get away with it, because
' the float error for a VBA double is < ±0.5 with numbers smaller than 2^32

dblOverflow = (1# * b * MOD_ADLER) + a

If dblOverflow > LONG_LIMIT Then  ' wraparound 2^31 to 1-(2^31)
   
    Do Until dblOverflow < LONG_LIMIT
        dblOverflow = dblOverflow - LONG_LIMIT
    Loop
    CheckSum = 1 + dblOverflow - LONG_LIMIT
    
Else
    CheckSum = b * MOD_ADLER + a
End If

End Function