#EXCEL APPLICATION ONTIME CODE#

Click image to enlarge.Īdding a clock to the Application.Caption in code 1 line 7. A ClockClear procedure to break the loop between the procedures in points 1 and 2.įig 1: xlfAnimation :: WS Clocks - 1.It is important to understand that the Clock and ClockTick have a circular link and form a two procedure loop (see figure 2) One second is the smallest time interval available in the VBA language environment. This runs the Clock at regular intervals one second time steps in this case. A ClockTick procedure linked to the Clock procedure.A Clock procedure linked to a ClockTick procedure.The AppCap clock is also fired by the Workbook Open and BeforeClose events. Procedures can be run and stopped from on-sheet ActiveX controls. Five examples are shown in figure 1, and the code is provided in sections 1.1 to 1.5. The following example use the Application.OnTime method to control a number of clock interfaces.

Returns True if the specified time has arrivedĮxample 3: run the M圜ode procedure in 30 seconds from now - If Application.Wait(Now + TimeValue('00:00:30')) Then Call M圜ode Syntax Application.Wait method: (not part of this module) This example is similar to the Wait method False to clear set procedureĮxample 1: run the M圜ode procedure at 6:15 pm - Application.OnTime TimeValue('18:15:00'), 'M圜ode'Įxample 2: run the M圜ode procedure in 30 seconds from now - Application.OnTime Now + TimeValue('00:00:30'), 'M圜ode'. True (the default) to schedule a new procedure. If set, a procedure will not be run if both the EarliestTime and LatestTime have passed If omitted the, the application will wait until the procedure can be run. The name of the (sub) procedure to be run What should be modified, below, so that 01 and 02 above are satisfied?ĭtmNextRun = dtmNextRun + TimeSerial(0, cIngMINUTES, 0)Īpplication.OnTime dtmNextRun, 'GetQuotes'Ī(EarliestTime, Procedure, LatestTime, Schedule) This way will solve my problem, I mean, will it continues to run the macro, periodically, even if I am not using the same worksheet related? (I will leave Excel open on that workbook, no problem, but I would like to be able to use other worksheets, or leaving excel by itself, so to speak, without having the routine interrupted.Ġ3. What should I correct in the code below, to make it work?Ġ2. To try to avoid this unreliability, I found other EE posts related to alternative ways of using the Application.Ontime and tried the following 'structure', that does not work even once.Ġ1.

After a while, it stops working even if the workbook is still open (this I am sure) and the worksheet is still active (not sure), in the sense that I 'leave' Excel in that worksheet while I am working on another thing out-of-Excel. I guess I must be with the workbook open (no problem) and also with the worksheet active (this IS a problem).Ģ. I mean ALMOST successfully because the way presented above is not reliable, at least considering two things:ġ. Using some EE posts, I was able to run (almost) successfully the New_Quotes() every (say) 1 min, using the following 'structure':Īpplication.OnTime TimeToRun, 'New_Quotes'Īpplication.OnTime TimeToRun, 'New_Quotes()',, False I created a subroutine New_Quotes() that works as expected (testing using a button that, whenever pressed, runs it through a macro).

Excel application ontime