如何使VBA宏在后台连续运行? [英] How to get VBA Macro to run continuously in the background?

问题描述

我想监视一个值，并在满足某些条件时收到电子邮件通知.我有一个这样的宏:

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?

我之前的工作是使用VBScript打开一个不可见的电子表格，并且VBScript在后台连续运行以监视情况并可以正常工作，但是我的客户确实希望使用GUI，并且该GUI必须包含在程序本身中.

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.

有什么想法吗?

推荐答案

使用Application.OnTime方法安排将在一分钟内运行的代码.

Use the Application.OnTime method to schedule code that will run in one minute.

您的代码将如下所示(未经测试):

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

您可能希望将下一个时间表的时间存储在全局变量中，以便Workbook_BeforeClose事件可以取消下一个时间表.否则，Excel将重新打开工作簿.

You might want to store the time of the next schedule in a global variable so that the Workbook_BeforeClose event can cancel the next schedule. Otherwise Excel will re-open the workbook.

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

然后您可以在计划的时间之间继续使用Excel.

You can then continue to use Excel between the scheduled times.

这篇关于如何使VBA宏在后台连续运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！