Ankur Pandey! sir lot of thanks. u waste ur time. and gave some ideas with code for my problem. i will try it. and inform u. once again thanks with sorry for my english regards kavi On Jan 20, 4:19 pm, ankur <ankurpande...@gmail.com> wrote: > 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
