Application ontime excel — Мир ПК

• Macro — строка с именем макроса;
• Arg1, Arg2, . — аргументы передаваемые макросу.

Например: Application.Run Macro:= «Расчет» — запускает макрос Расчет

• Procedure — имя выполняемой подпрограммы при нажатии клавиш;
• Key — строка, определяющая комбинацию клавиш, которая должна быть нажата. В этой строке можно также указывать специальные клавиши, используя следующие коды:
  • Backspace — или ;
  • Caps Lock — ;
  • Delete или Del — или ;
  • клавиша стрелка вниз — ;
  • End — ;
  • Enter (цифровая клавиатура) — ;
  • ESC — или ;
  • Home — ;
  • Ins или Insert — ;
  • клавиша стрелка влево — ;
  • Num Lock — ;
  • Page Down — ;
  • Page Up — ;
  • Return — ;
  • клавиша стрелка вправо — ;
  • Scroll Lock — ;
  • Tab — ;
  • клавиша стрелка вверх — ;
  • от F1 до F15 — от до .

Допустимо использование сочетания одновременно нажатых клавиш. С этой целью для перечисленных трех клавиш установлены следующие коды:

• Shift — +;
• Ctrl — ^;
• Alt — %.

В примере процедуре Амортизация назначена комбинация клавиш «Ctrl»+»+» , а процедуре ПроцентнаяСтавка — Shift+Ctrl+» стрелка вправо » :

• Text — строка, задающая текст команды Правка | Повторить (Edit | Repeat) ;
• Procedure — имя подпрограммы, выполняемой при выборе команды Правка | Повторить (Edit | Repeat) .

• EarliestTime — момент запуска процедуры;
• Procedure — имя процедуры;
• LatestTime — если на момент запуска процедуры, Excel не может ее запустить в силу того, что выполняется другое действие, то параметр LatestTime определяет последнее время ее запуска. Если этот аргумент опущен, то Excel будет ждать до тех пор, пока не сможет выполнить эту процедуру;
• Schedule — допустимые значения: True (выполнение процедуры откладывается на сутки) и False (во всех остальных случаях).

В следующем примере демонстрируется, как запустить процедуру Очистка на выполнение через 15 секунд от текущего времени:

На следующем шаге мы рассмотрим события этого объекта .

Schedule a Macro with VBA Application.OnTime

The VBA Tutorials Blog

Programmers love to make things more efficient. Pushing buttons just to make a code run is particularly annoying, especially when the code is supposed to run at the same time every time. Fortunately, Excel makes scheduled macro automation simple with the Application.OnTime method.

The Application.OnTime Method

The Application.OnTime method is part of the Application object, which means it lives inside Excel’s main application. If you’re using Intellisense, when you type Application.OnTime , you should see this:

Notice there are four arguments, two of which are optional:

• EarliestTime is the time you want your specified macro to start running
• Procedure is the name of the macro you want to call, which can be in the same workbook or another workbook
• LatestTime is optional, and it specifies the last time the macro can be run. It puts a limit or deadline on the latest time Excel will attempt to start the macro, and if the deadline is missed, the macro is not executed
• Schedule is used for canceling previously scheduled OnTime macros

Scheduling the Macro

There are many ways to schedule your macros to run using the OnTime method. The most common ways are via specific times (1am, 11:45, 19:30) or a time relative to the existing time. You can also make other scheduling decisions, like scheduling a macro to run relative to a future date based on some user input.

Again, the argument EarliestTime is your start time, and the scheduling mechanism will try to execute the specified macro at this time. As long as nothing prevents the called macro from running, this is synonymous with the start time. Conversely, if there is something preventing the called macro from running, LatestTime is essentially the time at which Excel stops trying to run the macro.

Specific Times

If a task must be run every day at the same time, it makes sense to apply the specific time technique. Let’s say you have to run a macro every day at 7am before you come into the office. You would execute a code snippet like this to make sure your macro runs before you even arrive:

In this example, early_morning_task is the name of the macro you have to run at 7am.

You could also use the TimeSerial function or the CDate function if you were grabbing your times from user-input variables:

Make powerful macros with our free VBA Developer Kit

There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier.

Notice the use of CInt to ensure the value going into TimeSerial is an integer. Also, note that this setup uses 24-hour format, and you will need to account for that by explicitly forcing users to use 24-hour formats, making an educated guess (programmatically!), or by offering the option of entering AM/PM and calculating the 24-hour format of the input.

Relative Times

You can also schedule macros to run at relative times. The most common way is to schedule it relative to the current time. You would do this by using the Now function to grab the current time then adding some amount of time to it.

The DateAdd function is a good option if you want to add two times together, and it plays well with the Now function. The following snippet fires off the macro nseconds from now, where nseconds is determined by the user.

If you’re scheduling over longer time periods, you can change the “s” to an “m” for minutes or “h” for hours and adjust the prompt.

Remember that a macro is executed line-by-line, so if the InputBox opens at 19:33:09 and the user waits 10 seconds then enters 15, the macro will fire at 19:33:34, not at 19:33:24.

Specifying the Macro

When you only have a single workbook, you can reference the macro directly by using its name. To write more robust code, you can specify the module to ensure no future changes to the workbook, like adding another module with an identically-named macro, will cause issues.

In this snippet, we are running the macro_to_schedule from Module2. If the Application.OnTime line runs in Module1 and there is a macro entitled macro_to_schedule there, we must specify we want the identically-named macro from Module2 . It’s certainly allowed, but I don’t recommend naming your macros exactly the same in different modules because it can become confusing for a human.

Scheduling Macros in Other Workbooks

Believe it or not, you can extend the Application.OnTime method to other workbooks, even if they have identically-named macros. I have plenty of workbooks that have subroutines named main , for example.

Keep in mind that Application.OnTime lives at the application level and schedules there, so you can run the OnTime method from one workbook and it will execute macros in another workbook as long as you properly specify the name of the other workbook. A call to a workbook named Other Workbook.xlsm with a macro named main in it will look like this:

Don’t forget that Excel uses ! to separate the workbook name from the Modules and Sheets but uses . to separate the Module/Sheet from the macro.

Scheduling Macros in Closed Workbooks

This extends even further to closed workbooks. Since the scheduling happens at the application level, as long as the application is open, the scheduled macro should execute. A user can close all of the workbooks but leave the application running and it will be fine. In other words, all the workbooks can be closed but Excel must remain open for the OnTime method to operate.

To call a macro in a closed workbook, you need to supply the full filepath to the workbook. Other than this, it’s exactly the same as scheduling a macro in another workbook:

Important: the workbook will open and try to execute the macro. If security settings are such that the opened workbook is not trusted, the macro cannot run until someone clicks “Enable Macros”. Make sure you set the workbook to be trusted before using this method or you won’t get much automation benefit.

Automation and Recursion

If you run the scheduler once, you will get a single scheduled event. However, if you need to run the macro with the scheduler to schedule it every day, you just shifted the burden from running the macro directly to running the scheduler.

There are times this is useful, like when you cannot determine programmatically when you will need the macro, but you will know before the end of the day and it must be run overnight. In that case, you could run the scheduling macro before you go home only on days you need it.

But if you know your macro will run every day no matter what, you can actually automate the scheduling itself. The best way to do this is write a recursive subroutine, which is a subroutine that calls itself.

There are two common ways to do this: call the scheduler macro from the task macro or roll everything into one and make the scheduler macro the same as the task macro.

First Method
This is a code block using two separate subroutines, which makes breaking them apart easier:

This will run task_sub every day at 5am. Notice how the task_sub macro calls the scheduler macro at the end, which queues up the next execution of the macro for the following morning.

Second Method
This method calls the scheduling method Application.OnTime at the end of the macro, although you can make it the beginning or anywhere in between, if you’d like. The main drawback is you must run the full task every time. It also violates the etiquette of breaking code into more readable chunks with specific purposes. But nevertheless, it works just as well.

Continuity and Canceling

For Application.OnTime to work, the Excel instance in which it was run must remain open. It is possible to open more than one instance of Excel, and that may be useful if you use Excel often and habitually close the main window. If the instance is closed, all scheduled information will be lost — even if Excel is reopened. This is very important to remember.

If you need to run a macro at a certain time each day even if Excel is closed, you’ll have to call the macro from a file, like a .vbs file, and schedule your macro using the Task Scheduler.

There is also the possibility that a scheduled task must be canceled. This can be done by quitting the instance of Excel in which it was created, but doing this will nullify all scheduled tasks. What if you only wanted to cancel one schedule macro? To keep the Excel instance open and schedule a particular task, the exact time and name of the scheduled subroutine must be used. Then the Schedule parameter of the OnTime method should be set to the Boolean False .

This code block has a scheduler macro and a cancellation macro:

To cancel a scheduled task_sub macro, the user can simply run the cancel_macro routine. Notice the positional passing of parameters. If you don’t want to use blank positional arguments like here, use named arguments like this:

Because exact times are important, and sometimes different users might enter different times, you will need to store these scheduled times somewhere. One obvious place is in a cell that won’t be overwritten. Another, but more involved option, is to write the time and macro name variables to a simple text file. A third, even more involved option, is to write custom properties for the workbook in which the scheduler resides. However you do it, it is a good idea to store the times somewhere or risk having to close Excel to cancel a macro you “lost.”

Stability

Automation is great, but only if the code is stable and what you expected to happen actually happens. If your application is meant to run a macro at a certain time every day and you plan to automate it with recursion, it is very important that Excel remains stable. That could mean manual garbage collection, learning about Windows and how the operating system interacts with Excel, and error trapping.

In fact, error trapping is extremely important for rapid automation in VBA. If an application is designed to run every 5 minutes and there is no error trapping, you’re almost guaranteed to come back to a run-time error or useless output. This becomes particularly important when no humans are monitoring the program. Computers won’t fix themselves, so you’ll need a way to alert humans. One way to do this is to send yourself an email once an error is encountered.

Don’t neglect stability and error trapping, especially in unattended automation scenarios. Alert humans to the errors.

Conclusion

Application.OnTime makes scheduling macros easy, as long as Excel remains open. Since scheduling inherently involves some future time, the OnTime method can be called and your macro will run later. This makes recursion easy because the code is not executed immediately like most other VBA macros. That means automation becomes easy, and that’s the point of writing macros, right? It’s certainly one of the most prominent uses of Application.OnTime .

The most important caveats are that 1. Excel, the application, cannot be closed 2. cancelation requests require the exact naming and time used to schedule the task to properly cancel the task. 3. code and macros must be stable for long-term, reliable automation 4. error trapping is not to be dismissed

If you address these caveats satisfactorily, scheduling and automation in Excel using Application.OnTime can open many, many opportunities.

I hope you’ll take a minute to subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog.

Excel VBA

Application.OnTime VBA, Schedule Excel to Run Macros at Periodic Intervals or a Specified Time

User Rating: 5 / 5

Scheduling Excel to Run a Procedure at periodic intervals or at a specific time of day, with the OnTime Method. Automatically run macros.

Excel Application.OnTime Method — Scheduling OnTime Events

Use the Application.OnTime Method to run a procedure at specified intervals or at a specific time of day. Syntax: ApplicationObject .OnTime(EarliestTime, ProcedureName, LatestTime, Schedule). Using this method you can schedule to run a procedure in the future. You can either fix specific intervals, starting from now, when the procedure will run, or you can fix a specific time of day. The (Excel) Application Object represents the entire Excel application, and is the top-most object in the Excel object model. The EarliestTime and ProcedureName arguments are required to be specified while the other arguments are optional. The EarliestTime argument specifies the time when the procedure is to be run. The ProcedureName argument specifies the name of the procedure you want to be executed. With the LatestTime argument you can set the time limit for running the procedure viz. if you set the LatestTime to «EarliestTime + 20» and if meanwhile another procedure is being executed and Excel is not in ready mode within 20 seconds, this procedure will not run. Omitting the LatestTime argument will make Excel wait and run the procedure. Omitting the Schedule argument will default to True, which sets a new Ontime procedure. To cancel an existing OnTime procedure set earlier, specify False.

To fix specific intervals starting from now, to run the procedure, use «Now + TimeValue(time)». To fix a specific time of day for the procedure to run, use «TimeValue(time)». See below examples on using these.

Stop or Cancel a Running Procedure (using the OnTime method)

If you attempt to close the workbook while a procedure is being run using Application.Ontime, Excel will re-open the workbook, and leave it open post completion of the procedure. Hence, you will need to cancel the procedure at a certain point or time.

To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don’t pass the time to a variable, Excel will not know which OnTime method to cancel, as Now + TimeValue(«00:00:03») is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (use a Public variable to make the variable available to all Procedures in all modules) and then use it to cancel the OnTime.

Example 1: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.

The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

‘Dim as a Public variable and it will be available to all Procedures in all modules.
Public rTime As Date

Sub CellValueAutoIncr1()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure on crossing a specific cell value.
‘The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

‘To run a procedure at a specific time, use TimeValue(time) viz. TimeValue(«20:30:00») will run a procedure at 8.30 pm. To run a procedure at specified time intervals (say, from now), use Now + TimeValue(time) viz. Now + TimeValue(«00:00:05») sets the time interval at 5 seconds, at which interval the procedure will run.
‘set the time interval at 3 seconds, at which interval the procedure will run.
rTime = Now + TimeValue(«00:00:03»)
‘procedure named CellValueAutoIncr1 will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime EarliestTime:=rTime, Procedure:= «CellValueAutoIncr1» , schedule:=True
‘increment the value in cell A1 (in Active Worksheet) by 5, for each time the Macro is repeated:
Cells(1, 1).Value = Cells(1, 1).Value + 5

‘If you attempt to close the workbook while a procedure is being run using Application.Ontime, Excel will re-open the workbook, and leave it open post completion of the procedure. Hence, you will need to cancel the

procedure at a certain point or time.

‘stop the procedure at a specified point — the procedure will stop after cell A1 value crosses 25 for the first time.

If Cells(1, 1).Value > 25 Then

‘If you need to cancel an OnTime, you must provide the exact time that the event was schedule to take place.

‘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.

‘To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don’t pass the time to a variable, Excel will not know which OnTime method to cancel, as

Now + TimeValue(«00:00:03») is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (Public

variable rTime in this example) and then use it to cancel the OnTime.

‘cancel the procedure by setting the Schedule argument to False:

Application.OnTime rTime, «CellValueAutoIncr1», , False

End Sub

Example 2: This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.

The procedure should be entered in a Standard Module (select Insert>Module, in VBE code window).

Public eTime As Date
Dim count As Integer

Sub CellValueAutoIncr2()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.

eTime = Now + TimeValue(«00:00:03»)
Application.OnTime eTime, «CellValueAutoIncr2», , True
Cells(1, 1).Value = Cells(1, 1).Value + 5
count = count + 1

‘stop the procedure after it runs for 5 times:

If count = 5 Then

Application.OnTime eTime, «CellValueAutoIncr2», , False

End Sub

Example 3: Start the OnTime procedure automatically when the workbook is opened; stop the OnTime procedure automatically on closing the workbook. This Application.OnTime procedure sets reminders at specific times and auto-closes workbook at a specified time.

Add code (workbook procedures) to the Workbook module (ThisWorkbook):

Private Sub workbook_open()
‘to start the procedure automatically when the workbook is opened — add this code to the Workbook module (ThisWorkbook)

‘call SetReminder procedure:

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘to stop the procedure automatically on closing the workbook, add this code to the Workbook module (ThisWorkbook)

On Error Resume Next
‘call StopSetReminder procedure to stop the Application.OnTime:
StopSetReminder
‘save workbook before closing:

End Sub

The procedures to be entered in a Standard Module:

Public dTime As Date

Sub SetReminder()
‘This Application.OnTime procedure sets reminders at specific times and auto-closes workbook at a specified time:

On Error Resume Next
dTime = Now + TimeValue(«00:00:01»)
‘procedure named SetReminder will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime dTime, «SetReminder»

‘Close the workbook at the specified time:

If Time = TimeSerial(18, 30, 0) Then

End If
‘set OfficeClose reminder:

If Time = TimeSerial(17, 30, 0) Then

Application.OnTime dTime, «OfficeClose»

End If
‘set LunchBreak reminder:

If Time = TimeSerial(13, 0, 0) Then

Application.OnTime dTime, «LunchBreak»

End If
‘set CoffeeBreak reminder:

If Time = TimeSerial(11, 15, 0) Then

Application.OnTime dTime, «CoffeeBreak»

End Sub

Sub CoffeeBreak()

Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(«WScript.Shell»)
‘play the Beep sound, you can customize the sound / message you wish to play:
Beep
‘the Popup Message Box will automatically close by itself, without user action of clicking Ok:

strMsg = obj.Popup(«Coffee Break Sir!», vbOKCancel)

End Sub

Sub LunchBreak()

Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(«WScript.Shell»)
Beep

strMsg = obj.Popup(«Lunch Break Sir!», vbOKCancel)

End Sub

Sub OfficeClose()

Dim obj As Object
Dim strMsg As String
Set obj = CreateObject(«WScript.Shell»)
Beep

strMsg = obj.Popup(«Office Closing Sir!», vbOKCancel)

End Sub

Sub StopSetReminder()
‘Stop the Application.OnTime procedure named SetReminder

Application.OnTime dTime, «SetReminder», , False

End Sub

Sub CloseWorkBook()
‘close the workbook

Application ontime excel

There may come a time when you need to run a procedure automatically at a particular time every day.
You may want to refresh the data in your database every 5 minutes.
This can be used to run a macro at a later time or to execute a procedure at regular time intervals.
Starts a background timer that runs a macro at a specified time.
Each application can only maintain one background timer set by OnTime.
If you start another timer before an existing timer runs, the existing timer is canceled.
This method will only be executed if the file containing the procedure is loaded when this line of code is executed and if the file containing the procedure is currently loaded at the time it is meant to run.
Always use fully qualified macro names: Project.Module.SubName
In Excel there is also an Application.Wait

Excel

EarliestTime — The time when you want this procedure to be run.
Procedure — The time when you want this procedure to be run.
Latesttime — The latest time at which the procedure can be run. If you do not specify a ‘LatestTime’ then the macro will run when Excel is available after the ‘EarliestTime’ has passed. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.
Schedule — True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.

This will run as soon as the current procedure has finished.

This will run 10 seconds from the time this line of code it executed.

Cancelling a scheduled Procedure
It is possible to cancel a procedure that has been scheduled to run but you need to know the exact date and time it was scheduled for.
To cancel a scheduled procedure you must know the «EarliestTime» it was scheduled for.
Exactly the same syntax except you set the schedule paramater to False. This tells the application to cancel the schedule.

When — The time at which the macro is to be run. Can be a string that specifies a time (for example, «4:30 pm» or «16:30»), or it can be a serial number returned by a function such as TimeValue or TimeSerial (for example, TimeValue(«2:30 pm») or TimeSerial(14, 30, 00)). You can also include the date (for example, «6/30 4:15 pm» or TimeValue(«6/30 4:15 pm»)).
Name — The name of the macro to be run. Use the complete macro path to ensure that the correct macro is run (for example, «Project.Module1.Macro1»). For the macro to run, the document or template must be available both when the OnTime instruction is run and when the time specified by When arrives.
Tolerance — The maximum time (in seconds) that can elapse before a macro that wasn’t run at the time specified by When is canceled. Macros may not always run at the specified time. The macro will be delayed until Word has completed another task. If this argument is 0 (zero) or omitted, the macro is run regardless of how much time has elapsed since the time specified by When.

This will run as soon as the current procedure has finished.

This will run 10 seconds from the time this line of code is executed.

This example runs the macro named «Macro1» 15 seconds from the time the example is run.
The macro name includes the project and module name.

This example runs the macro named «Start» at 1:30 P.M.
The macro name includes the project and module name.

Cancelling a scheduled Procedure
A second call to the OnTime will cancel the first call

PowerPoint

The method Application.OnTime does not exist in PowerPoint.

Outlook

The method Application.OnTime does not exist in Outlook.

Returning the correct time
This can be done using the following function

Scheduled the Procedure

Important

The line of code to cancel a scheduled procedure will generate an error when you attempt to cancel a non existent procedure.
The «EarliestTime» parameter is the earliest time the procedure will be called and is not the definitive time. Another macro might currently be running.

Оценка статьи:

Загрузка…

Application ontime excel Ссылка на основную публикацию

wpDiscuz