top of page
  • John

A better Application.OnTime ... run your VBA code to the nearest millisecond (ish)

This post applies to Excel, Word and PowerPoint.


The Application.OnTime method is great if you want to call your VBA procedure (Sub or Function) to the nearest second. At least it is in Excel and Word … there is no such method in PowerPoint.


So what do you do if you want to run a procedure in, say, a quarter of a second (ish … see the 'downsides', below) from now? Or run a procedure multiple times per second? This post is about using the Windows API SetTimer and KillTimer functions within the user32 library to help with this.


Here's the code to declare those Windows API functions and make them available to VBA code, and then code to make use of them in a (more) user-friendly way. When I use this in my projects, I normally add this as a standard Module called UtilsTimers.

Please read the documentation comments for the Create() procedure … and for the 'different rules about further prefixing with the Project or file name', see the Microsoft docs for Application.Run in Excel, Word and PowerPoint.


Usage examples

In this first example, SourceOneOff calls UtilsTimers.Create with arguments that result in TargetOneOff being called in 250 milliseconds.

In this second example, SourceRepeating calls UtilsTimers.Create with arguments that will result in TargetRepeating being called in 100 milliseconds and will be passed the number 10 as an argument … TargetRepeating then repeatedly calls itself every further 100 milliseconds while subtracting 1 from that number such that it is then called 10 times in total.

In this third example, SourceOneOffCancel calls UtilsTimers.Create twice but then cancels one timer, leaving the other to run. To check that one timer is actually being cancelled, comment-out the line: UtilsTimers.CancelOne lId

In each of the examples, the value returned from UtilsTimers.Create is tested because a 0 value indicates that the SetTimer function failed to create the callback. While testing this return value is obviously 'good practice', I don't recall a case of the SetTimer function failing.


Any downsides to this? Yes!

  • As noted by the documentation comments for the Create procedure, the target procedure will only be called when the host application (ie Excel, Word or PowerPoint) is ready to run VBA code … as you can see from the code in the InternalCallback() procedure, the Application.Run method is used to actually call the target procedure and so the Application.Run 'rules' apply for each host application (see the links above for details)

  • It is not accurate to the nearest millisecond … on my device, passing a value of 250 for lCallInMs resulted in the target procedure being called somewhere between 238 and 273 milliseconds later with an average of around 260 milliseconds (the code is subject to the vagaries of the SetTimer function, Application.Run, VBA and the host application)

  • An unhandled error in your target procedure will force-close the host application … note that my target procedures, TargetOneOff and TargetRepeating, have error handling … you should do likewise

  • Timers remaining outstanding when the project file (Excel workbook, Word document, PowerPoint presentation) is closed can result in the host application force-closing either at the point the timer would have been called or when the file is next opened … to guard against this, you should call CancelAll from the Workbook_BeforeClose event (in Excel) or the Document_Close event (in Word). There is no equivalent event in PowerPoint … so just be careful!

Given all that, when would I use this? Only in the scenarios noted above: when I want better control over exactly when a procedure should run, or I want to run a procedure multiple times per second. Obviously, I'd always use it in PowerPoint as it does not have an Application.OnTime method!


When would I not use it? When I want to run a procedure in a few seconds or more from now and accuracy to the nearest second is okay, in which case Application.OnTime works just fine.


If you want to read more about the Windows API functions used in this post, see the Microsoft docs for SetTimer and KillTimer.

0 comments

Recent Posts

See All

Comentários


bottom of page