CODEWITHSUNDEEP
vbaexcel
Charles Clayton
Charles Clayton

Reputation: 17976

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?

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?

Upvotes: 5

Views: 33706

Answers (2)

ChipsLetten
ChipsLetten

Reputation: 2953

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.

Upvotes: 9

Orphid
Orphid

Reputation: 2852

I think you need to specifically process the application event stack with a DoEvents call. This allows user interactions with the spreadsheet to occur, where normally the macro would take precedence. You code would look something like:

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.

Upvotes: 2

Related Questions