Excel Simple Digital Clock Timer VBA Macro

In this video we’ll see how to create a simple digital clock timer in Excel using VBA macros. Your can start/stop the time, and the clock shows hours, minutes and seconds. It is a simple example to learn how to use the OnTime method of the Application object. Find the code and explanation in following post: excelmacroclass.blogspot.com/...
For more Excel VBA fun applications and games visit Excel Macro Fun: excelmacrofun.blogspot.com/
And for other Excel VBA learning resources and applications visit any of the other blogs of the Excel Macro Mania saga:
Excel Macro Class (excelmacroclass.blogspot.com)
Excel Macro Business (excelmacrobusiness.blogspot.com)
Excel Macro Sports (excelmacrosports.blogspot.com)
Soundtrack: Funk Le Nuit (dmusic studio)
Link: soundcloud.com/user-785417998...

[@rodrigolisboanascimento8794]

Very good

[@tusharpal4738]

It was too easy to understand, thank you sir 😊

[@rafaelislas1516]

Very easy and usefull thank you

[@Behappyalways309]

Awesome Explanation

[@ExcelMacroMania]

Thanks for the feedback!

[@markevananiel]

I hope you can make a tutorial wherein there is a time in time out feature and then it computes for the time lapsed. All this triggered by clicking and not by keyboard shortcuts.

[@ExcelMacroMania]

So you want to show the time elapsed between the mouse clicks? Time elapsed is simple, but capturing mouse left click is not easy. We can easily capture mouse right click and double click within the worksheet with the respective worksheet events.

[@josephdaquila2479]

For everyone's reference you could just as easily write the code in more of the (r,c) syntax
i.e.
Cells(2, 2).Value = Cells(2, 2).Value + TimeValue("00:00:01")
instead of
Range("B2").Value = Range("B2") + TimeValue("00:00:01")
and so forth

[@ExcelMacroMania]

Indeed you can use both, thanks for sharing! That's all explained in one of the first videos (Tutorial for beginners) that I recommend everyone to see to learn the basics:
Excel VBA Tutorial for Beginners (Part 4/10): The Range Object ua-cam.com/video/OQAbT6dttho/v-deo.htmlsi=EostZGft_tUQTehM
It's also covered with more details in the Excel VBA Objects series:
Excel VBA Objects: Range Object (Part 1) ua-cam.com/video/h7-R_9ConD8/v-deo.htmlsi=A2-794FveElyWaVO

[@mokkelb]

Good video! Is there a way to add milliseconds?

[@ExcelMacroMania]

Yes, three is. You need to use the SetTimer function of the Windows API. You can have a look at this other video to see how that function is declared and used: ua-cam.com/video/qatTWjgnkqo/v-deo.htmlsi=KbhT23qVsRqu1oNu
Then you just need to change the interval to 1 ms and call the clock macro that updates the time in the worksheet.

[@ExcelMacroMania]

And I will add to my list and try to make a video to explain how to do it, it may not be as straight forward to understand as I explained in my previous reply 🙂

[@helenasilva3176]

Hi! Can I do this on Power BI?

[@ExcelMacroMania]

I don't think so. As far as I know, there is no macro capability in Power BI unless using some external API or something like that.

[@surontube]

Its running on other workbooks and sheets and throwing error on type mismatch. How to write an if condition to run only for a specific workbook ?

[@ExcelMacroMania]

You need to specify in which workbook and worksheet you add the time. The code shown here is writing the time in cell B2 in the active sheet and active workbook. If you activate any other sheet or workbook, it will write the time there. To change that, instead of just Range("B2").Value = .... you need to say Workbooks("Workbookname").Sheets("sheetname").Range("B2").Value = ...

[@adriandelosreyes5657]

Hi sir may i ask if you fix the error? Type mismatch? I also encounter that error.

[@ExcelMacroMania]

@@adriandelosreyes5657 This is just an example of a simple timer running only on that workbook and sheet. If you have more sheets or workbooks you MUST of course reference where the timer shows. Please check the tutorial for beginners in this channel of my blogs to learn those basics.

[@adriandelosreyes5657]

Hi sir, may i ask i have encounter an error about mismatch when i open another workbook.

[@adriandelosreyes5657]

The file name of mu bookwork is Book13 and sheet 1 how i can input this on code module?

[@adriandelosreyes5657]

Dim TimeOn As Boolean
Sub starttime()
If timerOn = false Then
Range (“B2”). Value = 0
Range (“B2”). numberFormat = “hh:mm:ss”
TimerOn = True
Call Settimer
End if
End sub
---------------
Sub Settimer()
Dim Timetocall as date
If TimerOn = True Then
TimetoCall = Now + TimeValue(“00:00:01”)
Application.OnTime TimetoCall, “movetimer”
End if
End sub
------------------
Sub MoveTimer()
If TimerOn = false Then
Range(“B2”).Value = Range(“B2”) + TimeValue(“00:00:01”)
If range (“B2”). Value

[@ExcelMacroMania]

That's because we did not reference a specific workbook in this example (I am only working with 1 workbook). If you have more than 1 workbook open and you are moving from one workbook to another while the macro is running, you MUST of course reference in which workbook you want the timer to display. To do that, you just add the workbook before the worksheet and range where you update the time. For example, for your Book13 that would be:
Workbooks("Book13").Sheets("Sheet1").Range (“B2”). Value = Range(“B2”) + TimeValue(“00:00:01”)
Please note that if the has been saved you need to add the extension: Workbooks("Book13.xlsm")....

[@michaelbowen2239]

I keep getting an error that the excel macro MoveTimer cannot be run, either because the macro is not available or all macros may be disabled. It runs other macros, but seems to be getting stuck on the Application.OnTime function. Any ideas?

[@ExcelMacroMania]

No idea why's that. Check with Debug.Print or MsgBox what you get with Now + TimeValue("00:00:01"), it might be your system returns time in a different way? ... See the macro code in this post: excelmacroclass.blogspot.com/2020/09/simple-digital-clock-timer.html

[@michaelbowen2239]

@@ExcelMacroMania It is the Application.ontime method that it is catching on. I was thinking it was that the macros were disabled, but that doesn't fit. How does the debug.print work?

[@ExcelMacroMania]

@@michaelbowen2239 Debug.Print(Now + TimeValue("00:00:01")) or MsgBox (Now + TimeValue("00:00:01")) ... and see if that makes sense, if it's a valid date/time accepted by Application.OnTime

[@michaelbowen2239]

@@ExcelMacroMania I got it figured out. In the MoveTimer sub the application.ontime it needed to know that the sub was in the Module1. The line was: If StopWatchTimerOn = True Then Application.OnTime TimeToCall, "Module1.MoveStopWatchTimer". I also made a count down timer and a stop watch out of it.

[@ExcelMacroMania]

@@michaelbowen2239 Glad to hear that!

[@MrPaunty]

Its Slow, i mean performance lag is there

[@ExcelMacroMania]

That may be due to a limitation by your system/CPU and/or using an older Excel version (or 32 vs 64 bit), etc. A workaround for slower systems is using the time difference, so get the start time first, and then update the clock with the difference:
1. Declare at module level
Dim StartTime As Date
2. Initialize the start time in the first procedure (StartTimer)
StartTime = Time
3. Update the clock with the time difference in the other procedure (MoveTimer)
TimeDiff = Time - StartTime
Range("B2").Value = TimeDiff
If Range("B2").Value < TimeValue("10:00:00") Then Call SetTimer