How can I control the macro's running time?
 

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

How can I control the macro's running time?
 

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

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49560

How can I control the macro's running time?
 

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.
--
- K Dales


"Ming" wrote:

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

How can I control the macro's running time?
 

You can't do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ming" wrote in message
...
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

How can I control the macro's running time?
 

I misread your question. Ignore my reply.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
You can't do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ming" wrote in message
...
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

How can I control the macro's running time?
 

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

"K Dales" wrote:

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.
--
- K Dales


"Ming" wrote:

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