hi memonkavi
this a extract from a website http://www.cpearson.com/excel/OnTime.aspx
perhaps this can help you
As parameters, the OnTime method takes a specific data and time at
which it should run the procedure and the name of the procedure to
run. It is important to remember that you provide the date and time to
run the procedure, not an offset from the current time. If you need to
cancel an OnTime, you must provide the exact time that the event was
schedule to take place. There is no way to tell Excel to cancel the
next OnTime event or to cancel all the pending OnTime events.
Therefore, you need to store the time at which the procedure is to run
in a Public variable and use that variable's value in calls to OnTime.
For example, declare Public variables in a standard code module,
outside of and before any procedure (Sub or Function) declaration:
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
SectionBreak
Starting A Timer
To start a repeatable timer, create a procedure named StartTimer as shown below:
Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
This stores the time to run the procedure in the variable RunWhen, two
minutes after the current time.
Next, you need to write the procedure that will be called by OnTime.
For example,
Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub
This procedure executes whatever code you include in it, and then at
the end calls the StartTimer procedure to schedule another OnTime
event. This is how the periodic calls are implemented. Note that if
you close the workbook while an OnTime event is pending, Excel will
re-open that workbook to execute the procedure and will not close the
workbook after the OnTime event is finished.
SectionBreak
Stopping A Timer
At some point, you or your code will need to terminate the OnTime
schedule loop. To cancel a pending OnTime event, you must provide the
exact time that it is scheduled to run. That is the reason we stored
the time in the Public variable RunWhen. You can think of the RunWhen
value as a unique key into the OnTime settings. (It is certainly
possible to have multiple OnTime events pending. In this, you should
store each procedure's scheduled time in a separate variable. Each
OnTime event needs its own RunWhen value.) The code below illustrates
how to stop a pending OnTime event.
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _
Schedule:=False
End Sub
SectionBreak
Using Windows Timers
In addition to Excel's OnTime method, you can use the Windows Timer
functions provided via Window API. Windows Timers are automatically
rescheduled and will continue to "pop" until you terminate the timer
with the KillTimer API function. With a Windows Timer, you provide the
interval, in milliseconds, that the timer will "pop". The timer will
"pop" at that interval until terminated with KillTimer.
These procedures require Excel 2000 or later, since we use the
AddressOf operator. The code will not work in Excel 97 or earlier
versions.
caution A NOTE OF CAUTION: If the code executed by the timer changes
a cell value, and you are presently in edit mode in Excel (e.g.,
entering data in a cell), Excel will likely crash completely and you
will lose all unsaved work. Use Windows timers with caution.
To use Windows timers, paste the following code into a standard code module:
Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long) As Long
Public TimerID As Long
Public TimerSeconds As Single
Sub StartTimer()
TimerSeconds = 1 ' how often to "pop" the timer.
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub
Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub
Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
''''''
' This procedure is called by Windows. Put your
' code here.
''''''
End Sub
Run the procedure StartTimer to begin the periodic timer. The variable
TimerSeconds indicates how often, in seconds, the timer is to "pop".
The SetTimer function takes a value in milliseconds, so the code
multiplies TimerSeconds by 1000.When the timer "pops" Windows will
call the procedrue TimerProc. You may name this procedure anything you
want, but it must be declared with the parameters exactly as shown
above. If the parameters differ from what is shown above, Excel will
crash. When Windows calls TimerProc, it passes the following
parameters:
Parameter Meaning
HWnd This is the HWnd (Windows Handle) of the Excel application. You
can ignore this parameter.
uMsg The message ID of a timer message, value of 275. You can ignore
this parameter.
nIDEvent This value indicates which timer is being "pop" if you have
more than one Windows timer in effict. This is the value that was
returned from the SetTimer API function.
dwTimer The number of milliseconds that Windows has been running.
This is the same value that you would get from the GetTickCount
Windows API function.
To terminate a Windows timer, use the EndTimer procedure shown above,
which calls KillTimer to actually terminate the timer.
ShortFadeBar
On 20/01/2010, memonkavi <memonk...@gmail.com> wrote:
> sir
> ex
> code
> do
> calculate
> loop until -----------------------
> here i want set a time limit
> pls help me
>
--
Have A Nice Time & Enjoy Life
Regards:
CMA Ankur Pandey
(Someone Different)
I'm not the best but i'm not like the rest~~
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
