Excel Simple Digital Clock Timer VBA Macro

Рет қаралды 20,294

Күн бұрын

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

Пікірлер: 29

@tusharpal4738 Жыл бұрын

It was too easy to understand, thank you sir 😊

@rafaelislas1516 Жыл бұрын

Very easy and usefull thank you

@Behappyalways309 8 ай бұрын

Awesome Explanation

@ExcelMacroMania 8 ай бұрын

Thanks for the feedback!

@josephdaquila2479 8 ай бұрын

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 8 ай бұрын

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 kzfaq.info/get/bejne/hbdxlbdnydrYmKM.htmlsi=EostZGft_tUQTehM It's also covered with more details in the Excel VBA Objects series: Excel VBA Objects: Range Object (Part 1) kzfaq.info/get/bejne/np1dhcJqqNXSdGw.htmlsi=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.

@mokkelb 10 ай бұрын

Good video! Is there a way to add milliseconds?

@ExcelMacroMania 9 ай бұрын

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: kzfaq.info/get/bejne/p8ekh7qbzNTPoaM.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 9 ай бұрын

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 3 ай бұрын

Hi! Can I do this on Power BI?

@ExcelMacroMania 2 ай бұрын

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 11 ай бұрын

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 11 ай бұрын

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 10 ай бұрын

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

@ExcelMacroMania 10 ай бұрын

@@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 10 ай бұрын

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

@adriandelosreyes5657 10 ай бұрын

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

@adriandelosreyes5657 10 ай бұрын

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 10 ай бұрын

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