Postponing a macro for 10 minutes

T

Tornados

Hello,

I have this setup:

When a certain cell changes value folllowing a calculation an automatic
email is sent. This uses Worksheet_calculation in which i call a
sendmail macro. This works fine.

However sometimes the cell value changes too much and too many emails
are being send. Therefore i would like to make sure that when a macro
event has been triggered, it will not send another email in for example
10 minutes..

Anyone? Thanks in advance. Ivo

ps. I already tried application.wait. however in this case the macro
continues to run and excel will sort of freeze for those 10 minutes,
which is not what i can afford :)
 
S

Stephen Bye

Store the time at which the e-mail is sent.
When the value changes, only send another e-mail if it is at least 10
minutes since the last one was sent, otherwise do nothing.
 
J

J.E. McGimpsey

One way:

Private Sub Worksheet_Calculate()
Static dLastSent As Double
If dLastSent = 0 Then dLastSent = Now - TimeSerial(0, 10, 0)
If CDbl(Now) >= (dLastSent + TimeSerial(0, 10, 0)) Then
'<your code here>
dLastSent = Now
End If
End Sub
 
T

Tornados

Hmm..

This code somehow doesn't work.. . It seems rather logical though..

Could there maybe be something wrong with the Double format?

It doesn't give an error yet the macro just does not get exucuted
anymore..

Regards, Ivo
 
J

J.E. McGimpsey

Works for me with a test book. The use of Double should not be a
problem - you could try switching to variants, but I've had more
trouble with them than with doubles.

Make sure the

dLastSent = Now

is inside the

If CDbl(Now) >= (dLastSent + TimeSerial(0, 10, 0)) Then
<your code
dLastSent = Now
End If

structure. If it were to come after the End If, dLastSent will be
updated every calculation instead of every time an email is sent.
 
Top