如何让 VBA 宏在后台连续运行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31210921/
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
How to get VBA Macro to run continuously in the background?
提问by Charles Clayton
I want to monitor a value and get an email notifications when certain conditions are met. I have a macro like so:
我想监视一个值并在满足某些条件时收到电子邮件通知。我有一个像这样的宏：
Do While True
Worksheet.Calculate
If Value > 10 Then
SendEmail
End If
Sleep 60*CLng(1000)
Loop
However, when I run this, it clogs up the entire program and will turn unresponsive if I try to do anything.
但是，当我运行它时，它会阻塞整个程序并且如果我尝试做任何事情都会变得无响应。
Is there anyway to accomplish this but have it run in the background or at least not crash the program?
有没有办法做到这一点，但让它在后台运行或至少不会使程序崩溃？
What I was doing before was using VBScript to open a not-visible spreadsheet and the VBScript ran continuously in the background monitoring the condition and worked fine, but my client really wants a GUI and for it to be in the program itself.
我之前所做的是使用 VBScript 打开一个不可见的电子表格，VBScript 在后台持续运行，监控情况并且工作正常，但我的客户确实想要一个 GUI 并且它在程序本身中。
Any thoughts?
有什么想法吗？
回答by ChipsLetten
Use the Application.OnTimemethod to schedule code that will run in one minute.
使用该Application.OnTime方法来安排将在一分钟内运行的代码。
Your code will look something like this (Untested):
您的代码将如下所示（未经测试）：
Sub CreateNewSchedule()
Application.OnTime EarliestTime:=DateAdd("n", 1, Now), Procedure:="macro_name", Schedule:=True
End Sub
Sub macro_name()
If Value > 10 Then
SendEmail
Else
CreateNewSchedule
End If
End Sub
You might want to store the time of the next schedule in a global variable so that the Workbook_BeforeCloseevent can cancel the next schedule. Otherwise Excel will re-open the workbook.
您可能希望将下一个计划的时间存储在全局变量中，以便Workbook_BeforeClose事件可以取消下一个计划。否则 Excel 将重新打开工作簿。
Public nextScheduledTime As Date
Sub CreateNewSchedule()
nextScheduledTime = DateAdd("n", 1, Now)
Application.OnTime EarliestTime:=nextScheduledTime , Procedure:="macro_name", Schedule:=True
End Sub
Sub macro_name()
If Value > 10 Then
SendEmail
Else
CreateNewSchedule
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=nextScheduledTime, Procedure:="macro_name", Schedule:=False
End Sub
You can then continue to use Excel between the scheduled times.
然后，您可以在预定时间之间继续使用 Excel。
回答by Orphid
I think you need to specifically process the application event stack with a DoEventscall. This allows user interactions with the spreadsheet to occur, where normally the macro would take precedence. You code would look something like:
我认为您需要使用DoEvents调用专门处理应用程序事件堆栈。这允许用户与电子表格进行交互，通常宏优先。你的代码看起来像：
Do While True
If Value > 10 Then
SendEmail
End If
Sleep 60*CLng(1000)
DoEvents
Loop
You could also construct a GUI with HTA if you wanted to remain with VBScript.
如果您想继续使用 VBScript，也可以使用 HTA 构建 GUI。
