How can I control the macro's running time?

[Guest]

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

 

[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

 

[Guest]

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.

 

[Chip Pearson]

You can't do this.


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

 

[Chip Pearson]

I misread your question. Ignore my reply.


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

 

[Guest]

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

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.