Lesson#189: Make a digital clock with VBA

In this article, we shall learn a VBA code to make a digital clock.

1. Go to the Developer Tab in Excel. If you haven’t activated the Developer Tab then see here how to do it.
2. Insert a Command Button by clicking on ‘Insert’.
3. Create a button on the sheet and double-click to enter the code.

To create a program in Visual Basic for Applications (VBA) that displays a clock in an Excel worksheet, you can use the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Update the clock every second
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"

End Sub

Sub UpdateClock()

' Get the current time
Dim currentTime As Date
currentTime = Time

' Display the current time in cell A1
Range("A1").Value = currentTime

' Update the clock again in 1 second
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"

End Sub

This code defines two subroutines: Worksheet_SelectionChange and UpdateClock. The Worksheet_SelectionChange a subroutine is an event handler that is triggered whenever the selection in the worksheet changes. It uses the OnTime method of the Application object to schedule the UpdateClock subroutine to run in 1 second.

The UpdateClock subroutine retrieves the current time using the Time function and displays it in cell A1 of the worksheet. It then uses the OnTime method again to schedule itself to run in another 1 second, creating a loop that updates the clock display every second.

To use this code, you will need to place it in a VBA module in your Excel workbook. When you open the workbook, the clock will start running and will continue to update until you close the workbook.