Excel VBA macro: How to implement running clock

Following video demonstrates how to implement simple VBA macro showing running clock in Excel worksheet. My solution has three macros:

  • RunClock: Sets cell (A1) value to current time by using Now()-function. Then it recursively calls itself in every one second until global variable clockOn = FALSE.
  • StartClock: Sets global variable clockOn = TRUE and calls RunClock
  • StopClock: Sets¬†global variable clockOn = FALSE
Recursive call is made using Excel’s function
Application.Ontime(EarliestTime, Procedure, LatestTime, Schedule)
In this case only first two parameters are needed:
  • EarliestTime: Time when Procedure is been run. In this case Now + TimeValue(“00:00:01”)
  • Procedure: The name of the procedure. In this case RunClock itself
If you want to create buttons for starting and stopping the clock, please check how to add button

Leave a Reply

Your email address will not be published. Required fields are marked *