vb监测Excel两个单元格变化，达到阈值响铃

需求

在Excel中实现监控两个单元格之间的变化范围，当达到某个设定的值的范围内时，实现自动响铃提示。

实现：

1. 首先设置Excel，开启宏、打开开发者工具，点击visual Basic按钮，然后在左侧双击需要监测的sheet。
2. 此时会打开一个代码编辑窗口，在窗口中粘贴代码，修改需要监控的单元格，然后保存。
3. 将响铃用的wav格式文件放入到D盘，以下以D盘为例，可自定义。
4. 此时回到Excel页面然后在对应的单元格编辑数字进行测试。
5. 以下代码实现了A1到B10这一组范围的多个单元格对，当有一个有变化达到条件时即可出发响铃。

Private Declare PtrSafe Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _(ByVal pszSound As String, ByVal hmod As Long, ByVal fdwSound As Long) As Long' 常量定义
Private Const SND_ASYNC = &H1        ' 异步播放(后台播放)
Private Const SND_FILENAME = &H20000 ' 参数是文件名
Private Const SND_NODEFAULT = &H2    ' 找不到文件时不播放默认声音' 模块级变量，用于记录已触发过的行和对应的值
Private triggeredRows As ObjectPrivate Sub Worksheet_Activate()' 初始化字典，在工作表激活时执行一次If triggeredRows Is Nothing ThenSet triggeredRows = CreateObject("Scripting.Dictionary")End If
End SubPrivate Sub Worksheet_Calculate()Dim i As LongDim threshold As DoubleDim soundFile As StringDim valA As Variant, valB As VariantDim diff As DoubleDim key As StringDim currentHash As String' 设置参数threshold = 2                   ' 阈值soundFile = "D:\xm3555.wav"     ' WAV 文件路径' 初始化 DictionaryIf triggeredRows Is Nothing Then Set triggeredRows = CreateObject("Scripting.Dictionary")' 遍历每一行For i = 1 To 10valA = Range("A" & i).ValuevalB = Range("B" & i).Value' 确保都是数字If IsNumeric(valA) And IsNumeric(valB) Thendiff = Abs(valA - valB)' 构造唯一标识符（当前 A 和 B 的值组合）currentHash = valA & "|" & valBkey = "Row" & i' 如果这一行没有触发过，或者值发生了变化If Not triggeredRows.Exists(key) Or triggeredRows(key) <> currentHash ThenIf diff < threshold Then' 播放声音If Dir(soundFile) <> "" ThenPlaySound soundFile, 0, SND_ASYNC Or SND_FILENAME Or SND_NODEFAULTElseMsgBox "警告音文件未找到: " & soundFile, vbExclamationPlaySound vbNullString, 0, SND_ASYNCEnd If' 更新记录为当前值triggeredRows(key) = currentHashElse' 差值不小于阈值，则清除该行记录（可选）If triggeredRows.Exists(key) ThentriggeredRows.Remove keyEnd IfEnd IfEnd IfEnd IfNext i
End Sub