VBA 函数可在时间范围内更改值时发出警报
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1237145/
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 function to alert when value is changed within a time frame
提问by SmartestVEGA
I have an excel cell where its value is updated every minute.
我有一个 excel 单元格，它的值每分钟更新一次。
What I need is an alert message box; while that cell value moves to a particular time.
我需要的是一个警报消息框；而该单元格值移动到特定时间。
Suppose a cell has value 10:
假设一个单元格的值为 10：
if it reaches 7 in one minute then I need a message box to alert that.
if it is not reaching 7 in one minute then I don't need any alert.
如果它在一分钟内达到 7，那么我需要一个消息框来提醒它。
如果它在一分钟内没有达到 7，那么我不需要任何警报。
Please help write me a macro for this.
请帮我写一个宏。
回答by shahkalpesh
Inside VBA editor for ThisWorkBook, you could write the following code
在 ThisWorkBook 的 VBA 编辑器中，您可以编写以下代码
Dim WithEvents SheetToWatch As Worksheet
Private Sub SheetToWatch_Change(ByVal Target As Range)
If Target.Address = "$A" Then
If Target.Value = 7 Then
MsgBox "changed to 7"
End If
End If
End Sub
Private Sub Workbook_Open()
Set SheetToWatch = Sheets("Sheet1")
End Sub
Basically the code sets the reference of Sheet1 to the variable SheetToWatchwhen the workbook opens. Declaring the variable using WithEventslets one capture the events.
基本上，SheetToWatch当工作簿打开时，代码将 Sheet1 的引用设置为变量。使用声明变量WithEvents可以捕获事件。
I am using Changeevent of the worksheet and checking if the cell that was changed is A2. If so, I am checking or comparing the value with 7 (it can be dynamic and depends on how you want to handle it).
我正在使用Change工作表的事件并检查更改的单元格是否为 A2。如果是这样，我正在检查或将值与 7 进行比较（它可以是动态的，取决于您想要如何处理它）。
See if this helps.
看看这是否有帮助。
回答by RBarryYoung
Cannot be done with a function, any way that I can think of.
不能用一个函数来完成，我能想到的任何方式。
If you control the mechanism that is updating the cell, then you could it call a VBA subroutine that you wrote, instead, have to send the alert and then update the cell from that routine.
如果您控制更新单元格的机制，那么您可以调用您编写的 VBA 子例程，而是必须发送警报，然后从该例程更新单元格。
If you do not control the updating mechanism, then the only thing that I can think of that might work is of the Cell, Range or Worksheet classes have a ChangedDate event that you could catch from VBA and do your alerting from there.
如果您不控制更新机制，那么我能想到的唯一可能有效的是 Cell、Range 或 Worksheet 类具有 ChangedDate 事件，您可以从 VBA 捕获该事件并从那里发出警报。
