How to make a VBA macro work in the background?

Question

I want to track the value and receive email notifications when certain conditions are met. I have a macro like this:

Do While True Worksheet.Calculate If Value > 10 Then SendEmail End If Sleep 60*CLng(1000) Loop 

However, when I run this, it clogs the entire program and will be irrelevant if I try to do something.

Is there a way to accomplish this, but does it run in the background, or at least not a program crash?

What I did before was using VBScript to open an invisible spreadsheet, and VBScript constantly worked in the background, monitoring the state and working fine, but my client really wants a graphical interface and for him 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 save the time of the next schedule in a global variable so that the Workbook_BeforeClose event can cancel the next schedule. Otherwise, Excel will open the workbook again.

 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 

Then you can continue to use Excel between scheduled.

Answer 2

I think you need to specifically handle the application event stack with DoEvents . This allows the user to interact with the spreadsheet, where usually the macro will take precedence. The code will look something like this:

 Do While True If Value > 10 Then SendEmail End If Sleep 60*CLng(1000) DoEvents Loop 

You can also create a graphical interface with HTA if you want to stay with VBScript.