How can I control the macro's running time?

M

Ming

I create one macro to grad the updated info and paste it to another spread.
then thing is it will take a while for the data update. so is there anyway to
control the macro's running time. i.e. after it excute some of the code, what
kind of code could halt the macro for 1 minute then keep running again.

thanks a lot for your help.

Ming
 
L

Leith Ross

Hello Ming,

The Sleep API will halt all execution from millseconds to hours. Add
new VBA module to your project anad paste the following code into it.

EXAMPLE OF STOPPING FOR 1 MINUTE
Sleep(60000)

Code
-------------------

'Time is in Milliseconds. 1 millisecond = 1/1000 of a second
Public Declare Function Sleep _
Lib "kernel32.dll" _
(ByVal dwMillisecs As Long) As Long
 
K

K Dales

Dim StartTime as Date
StartTime = Now()
' Begin the update process here
While Now() <= (StartTime + TimeValue("00:01:00")) ' 1 minute delay
DoEvents
WEnd
' continue your processing here.

What would be better would be to actually monitor your update, if you can,
to see when it is done. For example, if you are refreshing a query with
MSQuery or ADO, you could check the .Refreshing property to see if it is done
and use that as a condition to end the loop (keeping the time check as well
to give a 'timeout' value in case the query hangs). But without knowing what
you are updating I cannot give details or an example.
 
M

Ming

you're right on it. thanks a lot for your help.

K Dales said:
Dim StartTime as Date
StartTime = Now()
' Begin the update process here
While Now() <= (StartTime + TimeValue("00:01:00")) ' 1 minute delay
DoEvents
WEnd
' continue your processing here.

What would be better would be to actually monitor your update, if you can,
to see when it is done. For example, if you are refreshing a query with
MSQuery or ADO, you could check the .Refreshing property to see if it is done
and use that as a condition to end the loop (keeping the time check as well
to give a 'timeout' value in case the query hangs). But without knowing what
you are updating I cannot give details or an example.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Should I be content with the time code takes? 2
Want to have macro execute automatically when doc created 3
Getting Radio Buttons to work, that are inserted via AutoText 0
macro on time 1
paste special macro stops working when run a 2nd time 1
Time Out a MsgBox 7
how to write a macro to club data in multiple excel workbooks into asignle one??? 1
Running macro operations in serial fashion 6

Top