CODEWITHSUNDEEP
excelvbams-office
user1753675
user1753675

Reputation: 145

Macro runs every minute but I want a particular sub to run every 45 minutes

I have a spreadsheet that runs every minute between 8am to 4pm that imports files from a certain folder and does certain calculations

The calculations are done to check if a limit has been breached. If a limit has been breached then the spreadsheet needs to notify the user through a sound alert and email alert. The requirement is for the sound alert to go off every minute while the email alert is supposed to go off every 45 minutes as they dont want to be spammed with emails.

public sub fileImport()
     if between 0800 to 1600
         //do file import
         //do calculations
         if breach sound alert             
     end if   
     Application.OnTime RunEveryMinute, fileImport

     if there is a breach
         sendMail()         
         Application.OnTime RunEvery45Min, sendMail              
     end if

public sub sendEmail()
     //do email function

so how can i go about in calling the sendEmail sub only every 45 minutes instead of every minute?

Upvotes: 2

Views: 1640

Answers (2)

Stepan1010
Stepan1010

Reputation: 3136

You can just use the OnTime function:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub"

Sub StartTimer()
    RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub
'This stores the time to run the procedure in the variable RunWhen, two minutes after the current time.

'Next, you need to write the procedure that will be called by OnTime. For example,

Sub TheSub()
    ' Put your send email code here.
    StartTimer  ' Reschedule the procedure
End Sub

This example was from: http://www.cpearson.com/excel/OnTime.aspx

Good Luck.

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

Assuming this is in a loop just record the last send time and compare it to Now;

Public Sub sendEmail()
    Static lastSent As Date
    If DateDiff("m", lastSent, Now) > 45 Then
         '//do email function
         lastSent = Now
    End If
End Sub

(This will send the very first time its invoked too)

Upvotes: 4

Related Questions