Hi guys, here is a dynamic digital clock model in Excel I made today. The display looks very realistic.
I studied for this all the clocks in the house including the stove, microwave oven and washing machine clocks, plus some pictures on Google.
It will display your local computer time. It uses “sprites” as a method of creating the digits. I learned a lot while making it and I need to prepare a tutorial about this topic.
This would be a great beginner lesson in animation.
Just click the “Start-Pause” button and enjoy.
George
Hi Kamal, you are probably using a newer than 2003 version of excel. Remember this, whenever something does not update in VBA, use the line “DoEvents” liberally. 2007 and 2010 require more DoEvents than 2003 so that things update. Experiment with adding this statement in multiple places within the macro whenever your macro prevents the chart updating to take place. After it works try gettig rid of some by trial end error so that the macro keeps the updating running. Here is a generic example:
Sub Start_Pause()
[H3].Select
s = Not (s)
Do Until s = False
DoEvents
[B23] = [B23] + 1
[C91:AZ140] = [C31:AZ80].Value
DoEvents
Loop
End Sub
You can see above that I use DoEvents liberally. If I remove one of them it might behave like you said. It’s all trial and error.
Please give me some g+’s votes or some facebook likes whenever you come to the site. It helps my ranking. Thanks a lot, George
That was great man. Sure i will contribute for your rank. Good day 🙂
Thanks, Kamal!
Hey George,
Great tutorial. I sat down and finally understood the concept. but when i am ready with all the things, running the macro is not ticking the numbers. 🙁 They are static and changing only at the moment when i click some where on the graph or some where around data series. Expectation is after i run the macro, the clock should be ticking till is stop “run pause clk” macro, is’nt it ? but i can see the change only , when i click some where on chart. Can you help me on this , making it dynamic, 🙂 Thanks.
oh fantastic,admire can u prepare a book which explain such & different amazed excel works together
I hope to do this some day. Cheers, George
George,
I stumbled on your site this evening, and I am so excited to try some of the things you have created. You are so generous to post these tutorials for us can learn from. Thank you!
Shari
Thank you, Shari. I will resume the posting soon. George
its not run, Pls explain the below error
can not run the macro, the macro may not be available in this work book or all macros may be disabled
Suresh, call me George.
First question:
What version of Excel you are running?
It must certainly be a security setting issue.
Dear sir,
It is very fantastic and interesting clock but can you please make a video how to create this kind of clock in excel.
Best Regards
Shafiullah, I am sorry but I am too busy right now. There is already a written tutorial in PDF format you can check out. Maybe later I could make a video. Salaam, George
Hi, there
Great work. But I have a small question regarding of the digits. It seems you use a scatter chart to plot all the data. How did you make a scatter chart to have digits inside?
Illino,
Thanks for the comment. First create a chart and some nice letters out of free-form shapes. Then go to a shape, Shift-Edit-CopyPicture. Then go to a chart, click on a series, then paste and replace all the data points with a shape. If you click once then wait and click twice you replace only that single point with a picture. Again I discovered this by trial and error. I only use what works without hitches (problems, stutters etc). As a paranthesis, to change 7 pictures for example you need to have a 7 point graph, attach 7 DIFFERENT pictures on each point, then always keep 6 of them out far away in the invisible area of the chart (say -99,0) and one in the visible (say 0,0). Also make sure the “series order” is in the right order. This method is fast and good but the model becomes slow the more graphics you manipulate (takes 4-5 more memory than the graphics. I have better optioons and stay tuned since I will expose ALL of them in tutorials. For the day-night post however, due to the shear size of the photos, I use a VBA copy-paste procedure right on the chart. It takes just as much memory as the sum of all pictures. Had I used the first method to change backgrounds I would have needed about 22MByte :-(. George
Thank you, George. Brilliant idea. Web designer uses a similar method on CSS. I should think of that before.
You are welcome, of course you can just copy-paste but when you copy as picture then paste, the quality is better.
Pedie, C$5 provides the Y a positional “offset” on the which in our case happens to be 0. If the picture of the dial is not symmetrical you need to input a non-zero offset. $A9 is the size of the minute arm. You also need to use radians in the cos(), since all the formulas are in degrees and the trigonometric functions accept radians only. There are 360 degrees in one hour and also 60 minutes in an hour therefore you need 360/60 = 6 degrees per minute. Also you would like the minute arm to flow continuously not to jump 6 degrees every minute, therefore you add a much finer increment which is given by the sub-minute time run (1/60 of the angle of the seconds-arm). Don’t give up, just think about it. Try to forget everything on the site and do it yourself slowly. Spend a week if you need to. You will learn far more this way. Don’t forget, my PDF and the Excel file are just a “tease”, if should encourage you to try and experiment yourself, and of course give you the motivation to do something better. Cheers, George
I’d love to see a tutorial about this and some of your other really interesting implementations of charts in Excel. Truly superb stuff George!
Great Clock. Looks very realistic. Imaginative use of chart objects.
But….Excel is in constant calculation mode for the chart. As soon as a value is entered into any blank cell, the code stops, therefore the clock stops.
It’s based on a macro and of course it will stop when you type things in a cell.
The purpose of this clock (and of the entire site for that reason) is not to display the time while working in Excel but to educate you and show you the immense didactical capability of Excel because everything is open and visible within few cells. Try to explain a clock like this to regular people using C + some graphic package and you’ve got a mess on your hands. But in Excel almost anybody can understand it.