A simple timer for Excel VBA
Excel VBA does not have a timer control. The alternative is to use the Timer() Function, and that leaves much to be desired.
This code should be placed in a code module.
My need was to tend to an abandoned program that was waiting for user input. I used this timer so that, if time elapsed, dummy data could be entered and the application saved and closed. The problem(s) with using Timer Function is that each call starts a new timer. If the timer times out after the application has closed, the application will/can reopen to execute the sub specified in the call. It is easy to get more than one instance of the same event timing. If the first call has not yet timed out a second instance will start. Each instance will execute the associated sub causing no end of problems.
This timer uses three subs to resolve these problems. Timer_Start starts the timer which, as written, will run for only 1 second then calls CheckTime which will determine what happens next. Calling the timer in the workbook_close event, with an interval of 0 (zero) along with the 1 second interval ensure the timer is dead when the workbook closes. CheckTime checks for remaining time, and can start user notification if desired. StartTiming is called from anywhere in the project. The Sub can be called with Integer "Interval" = 0 to stop the timer. The maximum time is limited to the range of the Interger data type (32,767 which is just over 9 hours). If the timer is being called with time left on the first call, MyCount simply takes the new value of Interval with out having to start a new instance of Timer
Edited 3 Years Ago by Reverend Jim: changed to code snippet