Excel Simple Digital Clock Timer VBA Macro

[@tusharpal4738]

It was too easy to understand, thank you sir 😊

[@rodrigolisboanascimento8794]

Very good

[@rafaelislas1516]

Very easy and usefull thank you

[@Behappyalways309]

Awesome Explanation

[@ExcelMacroMania]

Thanks for the feedback!

[@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 kzbin.info/www/bejne/hYKkk4dsmdmnntEsi=EostZGft_tUQTehM It's also covered with more details in the Excel VBA Objects series: Excel VBA Objects: Range Object (Part 1) kzbin.info/www/bejne/nmiQg5JveNSheposi=A2-794FveElyWaVO

[@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.

[@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.

[@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: kzbin.info/www/bejne/p5LXhYqgnNOep9Esi=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.

[@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