How to get VBA Macro to run continuously in the background?

Question

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.

Any thoughts?

Answer 1

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

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

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