Pausing Code

[AnotherHiggins]

I've been teaching myself VBA over the last several months and am by no means a "coder", so please bear with me.

I've developed a macro that runs on a timer. Every morning it does some stuff in Access, saves and formats some Excel files, then sends several emails to various distribution groups via GroupWise. The problem is that I sometimes get an error on the .send line of code. I think that GroupWise just can't keep up with how fast the emails are being created and sent.

So I want to make the code pause for several seconds before sending each email. I've searched for and found threads about this, but haven't found a definitive answer to what I'm trying to accomplish.

I thought about something like this:

For i = 1 To 500000000
Next i

On my system, this takes between 14 & 15 seconds to run. It eats up a lot of system resources (as you'd expect). But since this is running early in the morning before I even get in, I don't really care about that.

so my question is: does this seem like a reasonable solution?

In thread181-77254, someone suggested using the following:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub test()
'...
Sleep (10000) 'to sleep 10 seconds
'...
End Sub

But in another thread, someone brought up the possibility that using the Sleep API wasn't safe in VBA?

I just want something simple that can be stuck into the macro.

Thanks in advance.

[tt]__________________________________________
My name is John, and I approved this post.[/tt]

To get the best answers fast, please read faq181-2886

 

[PHV]

Have you tried this ?
For i = 1 To 500000000
DoEvents
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AnotherHiggins]

PHV,

Now I have. The only difference I see is that it significantly increases the time it takes to loop through. Whereas 1 to 500,000,000 takes about 15 seconds to run without the DoEvents, it takes the same amount of time to go from 1 to 250,000 when the DoEvents is added in.

Is adding the DoEvents more programmatically sound for some reason?

Just wondering.

[tt]__________________________________________
My name is John, and I approved this post.[/tt]

To get the best answers fast, please read faq181-2886

 

[PHV]

To avoid this: It eats up a lot of system resources

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jm314]

Any suggestions on how to convert this to work off the system timer. I'd like to be able to incorporate a "pause" function for use on different systems so the length of the pause can be uniform between the systems. The only things I know about using the system timer are the TIMER function (as in Randomize Timer) and NOW which returns a time/date string.

I'm looking for two different things: (1) wait until a certain time occurs, ie. 1:00pm (2) wait for a certain amount of time to elapse, ie. 1.5 seconds.

 

[AnotherHiggins]

For part 1 of your question, this is what I'm using to have my code launch each morning:

Public RunWhen As Double
Public Const cRunWhat = "NameOfMacroToExecute"

Sub StartTimer()

Dim MyValue
RunWhen = TimeValue("13:00") 'Use 24 hour time - this will run at 1PM

Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

check out this site for info on using timers.

Hope that helps.

[tt]__________________________________________
My name is John, and I approved this post.[/tt]

To get the best answers fast, please read faq181-2886

 

[PHV]

If Excel VBA, take a look at the Application.Wait method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244