How to Automatically Update a Time or Clock in Excel Using VBA
Question details
The user wants to display a continuously updating clock or real-time timestamp in an Excel spreadsheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a real-time dashboard or tracking sheet that requires a live, ticking clock on the interface.
- Observed behavior
- The standard =NOW() function only updates when the worksheet is manually recalculated or edited, rather than advancing automatically like a live clock.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm), as a continuously updating clock requires VBA code to run in the background.
Create a Live Clock Using VBA Application.OnTime
Use a VBA macro to continuously update a specific cell with the current time at regular intervals.
To make a clock tick automatically, you need to use the Application.OnTime method in VBA. This schedules a macro to run itself repeatedly, updating the cell value every second.
Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new module for your code.
In the module, declare a public variable like 'Public RunTime As Double'. Create a Sub named 'StartClock' that writes the time to your target cell (e.g., Sheet1.Range("A1").Value = Time) and schedules the next run using 'RunTime = Now + TimeValue("00:00:01")' and 'Application.OnTime RunTime, "StartClock"'. Create another Sub named 'StopClock' with 'Application.OnTime RunTime, "StartClock", , False'.
Double-click 'ThisWorkbook' in the Project Explorer. Add a 'Workbook_Open' event to call 'StartClock' when the file opens, and a 'Workbook_BeforeClose' event to call 'StopClock'. This prevents the timer from reopening the workbook after you close it.
Return to your worksheet, right-click your target cell (e.g., A1), select 'Format Cells', and apply a custom time format such as 'hh:mm:ss AM/PM'.
Insert a Simple Timestamp Using the NOW Function
If you only need the time to update when you interact with the sheet, you can use built-in formulas without enabling macros.
Create VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides robust, built-in support for VBA macros. You can easily set up Application.OnTime procedures to run live clocks, automate repetitive tasks, and design dynamic real-time dashboards with zero hassle.
- 1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your .xlsm file containing the clock data.
- 2. Access the Developer tab: Navigate to the 'Developer' tab located on the top ribbon of the WPS interface.
- 3. Open the VBA Editor: Click the 'Visual Basic' icon to access the code editor, where you can paste or edit your timer macros.
- 4. Run your macro: Click 'Macros' to run your StartClock procedure manually, or assign it to a custom shape to act as a start button.
Frequently Asked Questions
Why does my =NOW() formula stop updating?
The =NOW() function is a volatile formula, meaning it updates automatically, but only when Excel performs a calculation (like when you edit a cell). It does not have an internal continuous timer to refresh itself second by second.
How do I prevent the VBA clock from running after I close the file?
You must use the StopClock macro triggered by a Workbook_BeforeClose event. By setting the Schedule argument in Application.OnTime to False before closing, you clear the timer. Otherwise, Excel might reopen the workbook to execute the pending schedule.
Will a running VBA clock slow down my workbook?
Running a macro every second can consume background processing resources. It may also clear your Undo stack continuously, making it difficult to undo typing or formatting mistakes while the clock is running. Use it selectively on dashboards rather than general data entry sheets.
Can I use shortcut keys to insert a time instead of VBA?
Yes. If you simply want to log the current time statically without updating it, select a cell and press Ctrl + Shift + ; (semicolon). This inserts a static time stamp that will never change.
