VBA & Macro Problems

How to Create an Excel Stopwatch That Keeps Running (VBA Guide)

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to create a functional stopwatch or timer within an Excel spreadsheet that continuously updates and runs in the background, allowing for simultaneous cell editing.

Product: Excel
Device & OS: not provided
Scenario: Tracking elapsed time in a spreadsheet without interrupting data entry or manual workbook manipulation.
Observed behavior: Standard Excel functions only update upon recalculation. To create a live, running timer that doesn't freeze the interface, specialized VBA macros are required.

Before you start

Ensure you have the Developer tab enabled in your Excel ribbon and remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to preserve the code.

Solution 1Recommended

Use the Application.OnTime Method for a Background Timer

This is the most reliable method for creating a stopwatch that updates every second in the background without freezing the Excel interface, allowing you to edit cells normally.

The Application.OnTime method schedules a macro to run at a specific future time. By having the macro schedule itself to run again in one second, it creates a continuous loop that yields control back to Excel in between updates. This ensures the UI remains responsive.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module.

Declare Global Variables

At the very top of the module, declare your variables so they persist between macro runs. For example: Public RunTimer As Date, Public StartTime As Double.

Write the Start Macro

Create a sub named StartTimer(). Inside it, record the current time to your StartTime variable, set RunTimer to Now + TimeValue("00:00:01"), and call Application.OnTime RunTimer, "UpdateTimer".

Write the Update Macro

Create a sub named UpdateTimer(). Inside, calculate the elapsed time by subtracting StartTime from the current time. Output this value to your desired cell (e.g., Range("A2").Value = elapsed). End the sub by rescheduling itself with another Application.OnTime call.

Write the Stop Macro

Create a sub named StopTimer(). Use the syntax Application.OnTime RunTimer, "UpdateTimer", , False to cancel the scheduled execution and stop the stopwatch.

Assign Macros to Buttons: To make the stopwatch user-friendly, insert shapes or form buttons on your sheet, right-click them, and choose 'Assign Macro' to link them to your StartTimer and StopTimer scripts.

Solution 2

Use a VBA Loop with DoEvents

An alternative approach using a continuous loop that yields execution to the operating system, allowing interface updates and cell edits.

Advanced Spreadsheets with Built-in VBA

Run Macros and Create Timers Seamlessly in WPS Office

WPS Spreadsheets provides comprehensive support for VBA macros, making it easy to create complex tools like custom stopwatches and background timers without sacrificing application performance.

1. Open the Developer Tab: Launch WPS Spreadsheets, open your workbook, and navigate to the 'Developer' tab on the main ribbon.
2. Access the VBA Editor: Click the 'VBA Editor' icon or press Alt + F11 to open the coding environment.
3. Implement Your Timer: Paste your Application.OnTime or DoEvents macro code, save the file as a Macro-Enabled Workbook, and assign your scripts to on-sheet buttons.

Fully compatible with Microsoft Excel VBA macros and .xlsm file formats.Lightweight architecture ensures fast execution of custom scripts.Intuitive Developer tab for straightforward macro management and editing.

Frequently Asked Questions

Why does my Excel freeze when running a timer macro?

If your macro uses a continuous loop (like a Do...Loop) without the DoEvents function, or relies on Application.Wait, it blocks Excel's main processing thread. Using the Application.OnTime method is recommended to prevent interface freezing and allow normal cell editing.

Do I need to save my file differently if it contains a stopwatch?

Yes. Because the stopwatch relies on VBA macros, you must save your file as an Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, all your timer code will be permanently deleted upon closing the file.

Can I format the stopwatch cell to show minutes and seconds?

Yes. Right-click the cell displaying the elapsed time, select 'Format Cells', go to the 'Custom' category, and enter 'mm:ss' or 'hh:mm:ss'. Ensure your VBA macro outputs an actual time value rather than a text string for this to work correctly.

How do I start and stop the stopwatch easily on the sheet?

You can insert shapes (Insert > Shapes) or form control buttons (Developer tab > Insert) onto your worksheet. Right-click the shape or button, select 'Assign Macro', and choose your specific Start or Stop macro from the list.

Excel & Data Problems