problem with Application.wait

[yetanotherid]

I'm using application.wait to pause a macro for 30 seconds. After the 30 seconds are up, I check for and process any incoming email. Once the email process macro is done, I loop back to wait for another 30 seconds. 99.99% of the macro's time is spent in application.wait.

My problem is I fired off task manager and saw that excel is using all of the cpu doing nothing but waiting for the 30 seconds to expire. It's as if application.wait is constantly reading a clock waiting for the designated time to pass.

Is there a better way of pausing for 30 seconds rather than using application.wait? My cpu's fan is spinning full tilt keeping the cpu cool while it essentially does nothing.

If I knew how to expose the macro to an external program, I could have the external program run on a 30 second schedule and fire off the email process macro instead of using wait. Suggestions?

 

[wigi]

What happens in the code? (you said it processes incoming emails, but can you be more precise ?)

Do you use Outlook as the email program?

I'm afraid that in Excel VBA you cannot do better than this, but I'm not 100% sure.

 

[shg]

See Help for Application.OnTime.

 

[yetanotherid]

After I posted, I realized my Google foo could improve and I started looking for 'application.wait alternative excel'. Sure enough, someone was talking about application.ontime which did the trick. Here's my revised code that doesn't waste CPU cycles just waiting...

Sub callProcessEmail()
Dim cmd
 
'quit at midnight
If Hour(Now) = 0 Then Exit Sub

' process any email
processEmail

' get new emails
ChDir "f:"
ChDir "F:\email"
cmd = "F:\email\getemail.bat"
Shell cmd

' do it again in 30 seconds
Application.OnTime Now + TimeValue("00:00:30"), "callProcessEmail"

End Sub

 

[Michael M]

Every 30 secs seems a lot of overkill.
Surely 3 - 5 minutes would be more than adequate to help reduce cpu usage.

As shg has suggested the On Time method is what you need.

Use this at the last line of you macro....this does 5 mins.

 Application.OnTime Now + TimeValue("00:05:00"), "YOUR MACRO NAME GOES HERE"

 

[yetanotherid]

Thanks SHG. Didn't see your post until after I posted.

 

[yetanotherid]

Every 30 secs seems a lot of overkill.
Surely 3 - 5 minutes would be more than adequate to help reduce cpu usage.

I don't know about you, but when I enter data in a form, I'm not particularly keen on waiting 5-10 minutes for a reply. I much prefer a reply to be in my inbox by the time I fire up my email client.

As it is, the reply email that processEmail generates has to filter through the cloud to reach my clients so the sooner I dispatch a response, the better.

 

[Michael M]

No, that's fine.
I just wanted to tick the box, to make sure we weren't "overusing" the system.
Sometimes a comment like the one I made, makes the user think about what they are doing, in case of the above.
Glad you got your prob sorted.