Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Excel VBA Application.OnTime


Reply
 
Thread Tools Display Modes
  #1  
Old 02-17-2010, 01:44 PM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default Excel VBA Application.OnTime


Edit: Maybe someone also has an example workbook of something like this already!

I wanted to create some type of program (in VBA) to pop up when its time for a user to take a break or lunch, or meeting etc.

This would require the user to enter a series of time.

My thought was to use a userform that allows the user to start with essentially inputting their start time, break1, lunch, break2, end of shift time

I just thought of a single time box, with insert before/insert after button and add these times to a listbox =p

I was thinking the best method would be some form of use with Application.OnTime to call a procedure when the time is reached, the OnTime procedure would then call the next OnTime method to run the next time in the loop

Ive not created anything like this before, and before starting a big endeavor I am looking for input on possible other methods, or complications I might run into. Is there maybe an API call I should use that would work better than this? Is the Application.Ontime going to cause interuption in the rest of the workbook (since a lot of other VBA code will be run besides the schedule program)
__________________
Josh

If Google = NoHelp Then PostHere = True

Last edited by Josh Hazel; 02-17-2010 at 07:37 PM.
Reply With Quote
  #2  
Old 02-19-2010, 03:41 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

The Application.OnTime is an easy and effective way to schedule tasks within Excel. The only problem you may run into, if your workbook runs a lot of code frequently is that the OnTime procedure will wait until the there is a break in code execution before it kicks off the designated procedure. So if the OnTime scheduled task fires while another macro is running, it will wait until that macro completes before calling the scheduled procedure. Does that make sense?

Also, the OnTime event gets cancelled if the workbook is closed. So if there's an issue of some sort and the workbook must be closed and reopened, you'll lose your scheduled tasks.

Here's an example of using a CommandButton to schedule an event to run 15 seconds later:
Code:
'Code placed in UserForm:
Private Sub CommandButton1_Click()
    Application.OnTime Now + TimeValue("00:00:15"), "SayHello"
End Sub

'Code placed in Module:
Public Sub SayHello()
    MsgBox "Hello!"
End Sub
Reply With Quote
  #3  
Old 02-19-2010, 07:36 PM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default

Im wondering, can I run multiple application.ontimes?

We are talking about scheduling start time of shift, start time of break 1, end time of break 1, etc ...

Can I run application.ontime multiple times and will run the procedure requested for each of those times, or do I need to have the procedure that is run trigger a new application.ontime for the next time in the schedule?
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
  #4  
Old 02-19-2010, 11:05 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

You can have multiple procedures scheduled by calling OnTime multiple times up front. In your situation, there shouldn't be any issues scheduling all of the times up front.
Reply With Quote
  #5  
Old 02-20-2010, 12:53 AM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default

Okay, and I tested it out, seems to work.... next step is to see how to update (ie stop the .ontime method) and remove all scheduled tasks - considering someone might update their timesheet... im sure that will prol be ez enuf to find that syntax online, whenever i add/delete/update a time i think ill have to remove all times and launch new times
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
  #6  
Old 02-20-2010, 01:32 AM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Contributor
 
Join Date: Jul 2009
Posts: 506
Default

You stop a timed task by issuing an ONtime command for the SAME time and procedure name, with the Schedule parameter set to False. The SAME time is crucial it is how VBA knows what to stop.
Reply With Quote
  #7  
Old 02-20-2010, 11:47 AM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default

Ive got a Q, if i launch 10 ontime methods, is there a way to loop through all of them without knowing their times?
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
  #8  
Old 02-20-2010, 02:39 PM
Bob Phillips's Avatar
Bob Phillips Bob Phillips is offline
Contributor
 
Join Date: Jul 2009
Posts: 506
Default

Short answer, No.

Why would you not know their times, the number you lauch is surely immaterial?
Reply With Quote
  #9  
Old 02-20-2010, 07:48 PM
Josh Hazel Josh Hazel is offline
Senior Contributor
 
Join Date: May 2008
Posts: 805
Default

Well, I was just thinkin outside the box =p
__________________
Josh

If Google = NoHelp Then PostHere = True
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->