{"id":14660,"date":"2018-03-22T22:10:46","date_gmt":"2018-03-22T22:10:46","guid":{"rendered":"http:\/\/learnexcelmacro.com\/wp\/?p=14660"},"modified":"2018-03-23T09:35:57","modified_gmt":"2018-03-23T09:35:57","slug":"what-is-application-ontime-method-and-its-usage-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2018\/03\/what-is-application-ontime-method-and-its-usage-in-excel-vba\/","title":{"rendered":"Schedule to close Workbook using Application.OnTime"},"content":{"rendered":"
Dear Friends,<\/p>\n
In this article I am going to teach you about Application.OnTime. <\/strong> I will also explain you the usage of this method with two examples:<\/p>\n This method is used to execute a procedure in Excel VBA at a specific time or after certain time has elapsed. Application.OnTime( EarliestTime , Procedure , LatestTime , Schedule )<\/em><\/strong><\/p>\n EarliestTime: <\/strong> This is a mandatory parameter and this is the time when you want to run your procedure. It accepts a variant data type. Follow the below steps to achieve this:<\/p>\n Copy and paste the following code in your ThisWorkbook<\/em> <\/span> Code window<\/p>\n Last but not the least step, copy paste the Save and Close workbook function which will be triggered at scheduled time in any of the regular module in your VBE project.<\/p>\n Before we proceed further, let see – How to check if excel was left idle?<\/span> If any of the above events are happening with workbook, idle duration will be reset back to zero again.<\/p>\n This is very useful when a workbook is shared to be used by more than one user. Incase any user left the excel open after working then, it will not be availbale for other users until he himself logs in and close the excel. Copy and paste the following code in any module in your VBE Project. Read basic tutorials – how to add modules in VBE. You can use this duration according to your need. In this function I have used it for 30 minutes.<\/p>\n Copy and paste the following code in your ThisWorkbook<\/em> <\/span>Code Window. <\/p>\n Last but not the least step – write your own procedure which you want to schedule it. Make sure that the name of the procedure exactly same as you have provided in the Application.OnTime method in above code.<\/p>\n Now Paste this SaveAndCloseWorkbook function code any where in any of the regular Modules (not in worksheet or workbook module)<\/p>\n Here I have provided only two basic usage of this function. You can use it even for triggering any of your procedure to run at specific interval etc. Dear Friends, In this article I am going to teach you about Application.OnTime. I will also explain you the usage of this method with two examples: How to schedule a workbook to close at specified time How to Schedule a workbook to close if left idle for specified duration This method is used to execute […]<\/p>\n","protected":false},"author":45,"featured_media":14681,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1674,1679],"tags":[],"class_list":["post-14660","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-macro","category-excel-macro-basics","category-excel-macro-beginner"],"yoast_head":"\n
\nHow to Schedule a workbook to close if left idle for specified duration\n<\/div>\n
\nLets have a look at the syntax of this Excel VBA method:<\/p>\nSyntax:<\/h2>\n
Where:<\/h3>\n
\nProcedure: <\/strong> This is also a mandatory parameter. It is the name of the procedure\/macro you want to run. It is a string type parameter.
\nLatestTime :<\/strong> This is an optional parameter. This is a maximum waiting time set by programmer for Excel to complete any ongoing activity like Cut, Copy, Paste, Save etc. and then start this procedure. Note that, if other ongoing activities are not complete within the specified waiting time then the procedure will not run. Since this is an optional parameter, if omitted, Application.OnTime method will wait till excel does not finish all its activities.<\/strong>
\nSchedule :<\/strong> This is an optional parameter. This is a boolean type parameter. True<\/strong> is used to schedule new procedure. False<\/strong> is used to clear out previously scheduled procedure.<\/p>\nExample 1# Schedule to save and close workbook<\/h1>\n
Step 1#<\/h2>\n
\r\n
\r\nPrivate Sub Workbook_Open()\r\n On Error Resume Next\r\n ' Schedule the Save&CloseWorkbook procedure at 5:30 PM\r\n Application.OnTime VBA.TimeValue(\"17:30:00\"), \"SaveAndCloseWorkBook\", , True\r\nEnd Sub\r\n\r\nPrivate Sub Workbook_BeforeClose(Cancel As Boolean)\r\n On Error Resume Next\r\n 'Before closing the workbook, remove the schedule\r\n Application.OnTime VBA.TimeValue(\"17:30:00\"), \"SaveAndCloseWorkBook\", , False\r\n On Error GoTo 0\r\nEnd Sub\r\n<\/code>\r\n<\/pre>\n
Step 2#<\/h2>\n
\r\n
\r\nSub SaveAndCloseWorkBook()\r\n ThisWorkbook.Close SaveChanges:=True\r\nEnd Sub\r\n<\/code>\r\n<\/pre>\n
Example 2# Save and Close workbook, if left idle<\/h1>\n
\nHere I am checking following two events to determine if excel was left idle-
\nSelectionChange in Workbook Event
\nAny Change across workbook Event<\/p>\nWhat is use of this? <\/h3>\n
\nIn such case, this little macro will help. It will automatically save and close the workbook if it was left idle for certain time (you can configure whatever you wish to)\n<\/p><\/div>\nStep 1#<\/h2>\n
\nNote that the following 2 lines must be added at the top of the module. It should not be pasted after any function\/procedure in the module.<\/p>\n\r\n
\r\nPublic runTime As Double\r\nPublic Const DURATION_IN_HOURS = 0\r\nPublic Const DURATION_IN_MINUTES = 30\r\nPublic Const DURATION_IN_SECONDS = 0\r\n<\/code>\r\n<\/pre>\n
Step 2#<\/h2>\n
\r\n
\r\nPrivate Sub Workbook_Open()\r\n On Error Resume Next\r\n ' Set the runTime public varibale with the specified duration\r\n ' To change the time you can change in the public Constants\r\n ' defined in one of your module\r\n runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)\r\n \r\n ' Now schedule the Save&CloseWorkbook procedure with the specified duration\r\n Application.OnTime runTime, \"SaveAndCloseWorkBook\", , True\r\nEnd Sub\r\n\r\nPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)\r\n\r\nOn Error Resume Next\r\n ' As explained above to reset the scheduled time for this save and close procedure\r\n ' Scheduled - False parameter is used\r\n Application.OnTime runTime, \"SaveAndCloseWorkBook\", , False\r\n \r\n ' Now reset the runTime variable again based on the new time\r\n runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)\r\n \r\n 'Now re-schedule the Save&Close function with the new calculated time.\r\n Application.OnTime runTime, \"SaveAndCloseWorkBook\", , True\r\nEnd Sub\r\n\r\nPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)\r\nOn Error Resume Next\r\n ' As explained above to reset the scheduled time for this save and close procedure\r\n ' Scheduled - False parameter is used\r\n Application.OnTime runTime, \"SaveAndCloseWorkBook\", , False\r\n \r\n ' Now reset the runTime variable again based on the new time\r\n runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)\r\n \r\n 'Now re-schedule the Save&Close function with the new calculated time.\r\n Application.OnTime runTime, \"SaveAndCloseWorkBook\", , True\r\nEnd Sub\r\n\r\nPrivate Sub Workbook_BeforeClose(Cancel As Boolean)\r\n On Error Resume Next\r\n \r\n 'Before closing the workbook, remove the schedule\r\n Application.OnTime RunWhen, \"SaveAndCloseWorkBook\", , False\r\n On Error GoTo 0\r\nEnd Sub\r\n<\/code>\r\n<\/pre>\n
Step 3#<\/h2>\n
\r\n
\r\nSub SaveAndCloseWorkBook()\r\n ThisWorkbook.Close SaveChanges:=True\r\nEnd Sub\r\n<\/code>\r\n<\/pre>\n
Important:<\/h2>\n
\nIf you find any other interesting usage of this method, do not forget to share it with every one by commenting to this article.\n<\/p><\/div>\n<\/span>","protected":false},"excerpt":{"rendered":"