 | ||||
| ||||
API - Timer
Can Do
![Goto Guest book sign in page [Home]](files/guestbook.gif)
OK, you may or may not be aware that Excel has a time function ie.
From Microsoft Help
Time Statement:
Sets the system time.
Syntax
Time = time
Example;
Dim MyTime
MyTime = #4:35:17 PM# ' Assign a time.
Time = MyTime ' Set system time to MyTime.
And also
OnTime Method
Schedules a procedure to be run at a specified time in the future
(either at a specific time of day or after a specific amount of time has passed).
Syntax
Expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
Expression Required.
An expression that returns an Application object.
EarliestTime Required Variant.
The time when you want this procedure to be run.
Procedure Required String.
The name of the procedure to be run.
LatestTime Optional Variant.
The latest time at which the procedure can be run.
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
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 Optional Variant.
True to schedule a new OnTime procedure. False to clear a
previously set procedure. The default value is True.
These are handy, but you may require something more!
The following routines I have developed to use the Windows Timer functions in a similar fasion BUT with the added advantage in that you can use this to good effect in your routines and they do not hinder the Excel application eg. Ontime will pause when you are in edit mode hence the LastestTime optional method for the Ontime function. The API Timer runs seamlessly in the background.
Timed messagebox:
This routine, I first published @ the old MrExcel board uses this Timer technique to do what some said could not be done > dismiss a msgbox after a set time. Hers's what you need & how:
1) OS timer (API Timer function)
2) Sendkeys
3) and the AddressOf operator available in Xl2000 onwards, for Xl97 the addressof operator is not avialable BUT there is a work around that was provided by Ken Getz and Michael Kaplan. Published in the May 1998 issue of Microsoft Office & Visual Basic for Applications Developer (page 46).
The following code creates a timer with the specified time-out value that YOU specify using the SetTimer API. Because it is driven from the windows own timer function the speed of the computer doesn't affect it and no resources are taken away. But a snag occurs when using this in that you need to define the address of the function to be notified when the time-out value elapses (lpTimerFunc). For this you need the AddressOf operator.
The code that follows allows you to setup a timer so that @ the elapsed time it calls the callback function.
It is from here that you use the sendkeys command to deactivate the active window. The routine is handy for other procedures eg. getting a Printpreview for 5 secs then continuing with your code or when you want to do an Event asyncronously.
Below you will see code examples of
Timed printpreview screen:
This example uses the same technique, but dismisses the PrintPreview screen.
| ||||
What else can you do:
Well, that's up to you. I have used this technique to run events at predetermined times eg Animation sequences @ set intervals,
Use this technique to do the job of seamlessly timing events that won't tie up your processing.
The important thing to remember is that you MUST KILL the timer event.
While we are on Timers here are some short routines that uses the Winmm Dll to display an accurate time (thousands of a second)
Note: This technique is now incorporated in the Html addin for Xl2000 instead of using the IeTimer ocx, which some systems did not have and were experiencing problems getting the control to function. My thanks to David Mackinney for suggesting this.
Here is the example code to use the API timer with a message box. Note the use of Sendkeys within the TimerCallback routine. To copy this code scroll down within the Code frame and click on the button. This will copy the code to your clipboard, then just paste it into your VBA code module. Otherwise get a Working book (See above)
Thanks for visiting my site lucky visitor:
This page was last updated on: March 21, 2009
Copyright © 2002. XcelFiles. All Rights Reserved Ivan F Moala
Having trouble running Excel on your computer? You may need some serious laptop repair. Whether you're having problems with your laptop LCD screen or you think your laptop fan is running overtime, get your laptop in for repair before anything gets worse.
