Hi guys, here is the final part of tutorial about creating a digital clock in Excel.
It is a good tutorial about general use of sprites in animation, especially the first half.
Tell us your thoughts,
Building an Digital Clock in Excel – part #2 – a full digital clock model using the “digit” sprites
by George Lungu
Introduction:
– In this tutorial we will learn how to use the digit sprites created before. This is very important since a lot of the models used in this blog are based on animation in general and on sprites in
particular.
– We will start by building a 2-D scatter chart and showing how we can replace each individual point with a sprite. Combined with a little “count-up” macro we will prove animation
functionality on the chart.
– In the second stage we will insert a new spreadsheet and write a macro which updates the value of cell A29 with the current time and the value of the cell A30 with the current date.
– After that we’ll extract the hours, minutes and seconds using special spreadsheet functions and use the numbers as reference to six tables, each containing information to run the six
“digit” sprites groups on a common chart, hence finalizing the clock model.
<excelunusual.com> 1
A basic sprite animation for a single digit display:
– Rename the “Sheet1” of the workbook “OneDigitAnimation”
– In the VBA editor section corresponding to “Sheet1” write the following macro:
Sub RunPause_Change()
If RunPause > 9 Then RunPause = 0
If RunPause < 0 Then RunPause = 9
Range(“A1”) = RunPause.ValueEnd Sub
-This macro will run the integer number in cell A1 from 0 to 9 and it will “roll over” after reaching 9 or 0.
– Create a spin-button with the range -1 to 10 and associate it to this macro.
– Verify the functionality of the button-macro pair.
<excelunusual.com> 2
The “sprite” driver:
– Create a increasing series from 0 to 9 in the range: “A5:A14” -> A5: “=1”, A6: “=A5+1”, copy A6 down to A14
– Pad the range “B5:B14” with zeroes
– Insert the following formula in cell C5: “=IF(A$1=A5,0,99)”
– Copy down C5 to C14
– Keep clicking on the button and you can see that only one number on the Y column is zero at any time (the rest are 99). That number’s position is defined by the value in cell “A1”. This is an extremely important setup for sprite animation and it will be used a lot from now on.
– If we plot the X-Y information on a 2D scatter chart and attach a different sprite to each of the 10 points we could see how we can keep all the sprites except one out of view (coordinate 99 is arbitrarily chosen well beyond the scatter chart axis range). Of course, during the run, the handle (cell A1) can be changed many times which produces the effect of animation.
<excelunusual.com> 3
– Select range “B4:C14” and insert a scatter chart
– Adjust both axes to ranges between -5 and +5
– Delete gridlines, delete both axes and axes titles and shape the chart to a rough square
– Double click the plot area rectangle and change the color to a dark green or something dark that you like
– Copy the “digit” sprites you created from the previous tutorial and paste all ten of them in the worksheet
– Select sprite “0” -> Shift -> Edit -> Copy Picture – > As Shown On Screen -> OK
– Click the spin button until cell A1 = 0
– Click on the data point on the screen then wait 1-2 seconds then click again until the cursor looks like a cross
– Paste. After that we can see a “0” on the chart
<excelunusual.com> 4
– Select sprite “1” -> Shift -> Edit -> Copy Picture – > As Shown On Screen -> OK
– Click the spin button until cell A1 = 1
– Click on the data point on the screen then wait 1-2 seconds then click again until the cursor looks like a cross
– Paste. After that we can see a “1” on the chart
– Select sprite “2” -> Shift -> Edit -> Copy Picture – > As Shown On Screen -> OK
– Click the spin button until cell A1 = 2
– Click on the data point on the screen then wait 1-2 seconds then click again until the cursor looks like a cross
– Paste. After that we can see a “2” on the chart
<excelunusual.com> 5
– Select sprite “3” -> Shift -> Edit -> Copy Picture – > As Shown On Screen -> OK
– Click the spin button until cell A1 = 3
– Click on the data point on the screen then wait 1-2 seconds then click again until the cursor looks like a cross
– Paste. After that we can see a “3” on the chart
– Select sprite “4” -> Shift -> Edit -> Copy Picture – > As Shown On Screen -> OK
– Click the spin button until cell A1 = 4
– Click on the data point on the screen then wait 1-2 seconds then click again until the cursor looks like a cross
– Paste. After that we can see a “4” on the chart
Continue the process until you finish with digit “9”, then you can check the functionality of the display by clicking extensively the spin button up and down.
<excelunusual.com> 6
Let’s introduce spatial offsets in the “sprite” driver table:
– Create two more spin buttons: View -> Toolbars -> Control Toolbox -> Click Design Mode -> Spin button (drag-create twice)
– Right click the left button: Properties -> (Name) = SpatialOffsetX, Max =20, Min=-20 -> OK
– Right click the right button: Properties -> (Name) = SpatialOffsetY, Max =20, Min=-20 -> OK
– B5: “=B$3”
– C5: “= IF(A$1=A5,0,99)+C$3”
– Copy down both B5 and C5 to row 14
– Write the following associated macros:
Private Sub SpatialOffsetX_Change()
Range(“B3”) = SpatialOffsetX.Value / 10
End Sub
Private Sub SpatialOffsetY_Change()
Range(“C3”) = SpatialOffsetY.Value / 10
End Sub
Verify the functionality of the sprite offset buttons =>
<excelunusual.com> 7
A parenthesis – how to extract various digits from a number:
– If we have a 2-digit number let’s say 47 (representing either hours or minutes or seconds) in a cell, what spreadsheet functions do we use to extract the first digit (4) and second digit (7) and place them in two different cells respectively?
– There are other ways to do it (I encourage you do it differently) but in this case I happened to just use these two functions: the “Right()” function and “Trunc()” function (you can do a search in the “Help” menu on them).
– The “Right()” is a text manipulation function so I found that I needed to multiply it by 1 in order to make it yield a number not text.
Let’s implement this in the spreadsheet:
– Type the following labels in the range A17:A19: “Number”, “First digit”, “Second digit”
– B17: “=45” (or any 1 or 2-digit number), B18: “=TRUNC(B17/10)”, B19: “=1*RIGHT(B17)”
– Verify the functionality of the formulas by typing various 1 or 2 digit numbers in cell B17
<excelunusual.com> 8
Getting time and date recorded on the spreadsheet:
The following macro is designed to be started or stopped by clicking the same button. For this purpose the Boolean variable “RunClk” was declared:
Dim RunClk As Boolean
———————————————————–
Sub RunPauseClk() “RunClk”
RunClk = Not (RunClk) ‘This statement in the macro logically “flips” the variable
and this has the effect of exiting the “Do” loop if the loop is
active, or of starting it if the loop is inactive.
Do While RunClk = True ‘This is the conditional “Do” loop used to display the time in
cell “A29” and the date in cell “A30”.
DoEvents
Range(“A29”) = TimeValue(Now) ‘Additional spreadsheet functions used: Extracts the number of hours
Range(“A30”) = Now()
Loop
End Sub
hour() – On our spreadsheet the macro will from a date update the time in cell “A29” and date and
minute() Extracts the minutes from a date time in cell “A30”
second() Extracts the seconds from a date
– We’ll calculate the hours in cell “A33” ”Cell A33: ”=HOUR(A29)”
– The minutes and seconds will be calculated in cell “B33” and “C33” respectively: Cell B33: “=MINUTE(A29)”, Cell C33: “=SECOND(A29)”
<excelunusual.com> 9
A tour of the digital clock spreadsheet:
The picture below contains the “seconds” part of the implementation:
Clock Scatter Chart: set the Y axis range to [-2, 2] and the X axis range to [-3.3, 3.3] then delete both axes.
“Seconds” Sprites: You can create them in either Excel or Power point. Select them all and resize them to your preference. I shrank my “seconds” sprites relative to the “hour” and “minute” sprites, and placed them higher to the right.
Spatial offsets of the “seconds” digits on chart (experiment with those)
Time inserted in the worksheet by the “RunPauseClk()” macro.
Button for triggering the “RunPauseClk()” macro.
Splitting of the “Seconds” number using functions right() and trunc() into most significant and least significant digits.
Separation of the time figure (from cell A29) Sprite driver in: hours, minutes for “seconds” and seconds.
<excelunusual.com> 10
A tour of the final digital clock spreadsheet (continuation):
Hours
Blinking Dots
Minutes
– Sprite driver for “minutes”
– Sprite driver for “hours”
– Sprite driver for “seconds”
-Logo (name) sprite driver
Sprite driver for the two dots between hours and minutes – I created two different sprites for the dots, one with the same color scheme as the digits and one darker. I alternate them every second which gives the impression of blinking dots.
<excelunusual.com> 11
Conclusions:
– A simple method for creating a sprite driver was developed
– Using the sprites from the previous presentation and the sprite driver a one-digit display was generated
– A step-by-step means of inserting the sprite group on a scatter chart was demonstrated
– A macro for updating the time was written
– Spreadsheet functions for extracting the hours, minutes and seconds, as well as splitting those numbers into digits were presented
– Map-like explanation sheets for the full digital models were shown
by George Lungu <excelunusual.com>
the part on page 2 with that code don’t work can you help me i got excel 2010
Let me look at it later today.
Nice ->Work -> Friend…
I Really Appreciate It.
Azhar
Very Nice work, really appreciate it
-Ramprasad
Good day.
Im having difficulties to insert the sprites, I tried to follow this instruction
“Select sprite “0” -> Shift -> Edit -> Copy Picture -> As Shown On Screen -> OK”
but failed. If not a burden to you, can you simplified instruction on how to do it?
Best Regards,
Joel
Joel,
What I meant is to select the sprite “zero” by clicking on it and then holding the shift button down, right click and go through the series of menu options Edit-copyPicture-AsShownOnScreen-OK. After this you will have the picture copied on the clipboard (but nothing happens yet). Then you go to one chart point of your choice and click it. The series on the chart will select. Wait for 1-2 seconds and click again on the same point and now only that particular point will be selected but not the rest. Right click on the top of the point and “paste” or just use the Control “V” shortcut.
A helping video is here (not so complete but it should give you an idea):
https://www.youtube.com/watch?v=GS5EgxCYQ_8&feature=plcp
Good day.
Im having difficulties to insert the sprites, I tried to follow the instruction but failed. If not a burder to ypu, can you simplified instruction on how to do it?
Best Regards,
Joel
i also appreciate the good work u have done. but i am facing the same problem like Shafiullah. for mine , at the begining while creating the VBA, there is a mesage that displays. “no object found”, pliz help me
Tabi, what version of Excel are you using? I am pretty sure it has to do with the security level being too high.
nice work , thank you so so much you helped me for what I was looking for . thank you for your efforts .
Dear Sir,
I appreciate your hard work regarding the creation of the digital and the analog watches in excel. But your toturial are not enough for any one to creat a clock himself. I request you to make a video with details and tell us how to make everything. I am trying to create both kinds of clocks but in vain. I can’t animate the digits on the face of the scatered chart. Please send me the whole details.
Thanks.
Shafiullah, Let’s see what I can do in the little time I can scavenge. Thanks, George
Dear Sir
I wish to create a digital clock but I can’t adjust both axes to range between -5 and +5, how can I do it.
My name is Menezes. I was trying to follow your tutorial digital clock but i could not understand how to Create a spin-button with the range -1 to 10 so i stopped there. Can u give me some clues. In the tutorial you do not say much. Please can you make one tutorial about how to create moving shape in your blog there is only excel sheet without the tutorial. Thanks a lot . This blog is doing a lot to me. By the way, I am using excel 2007.
Hi Menezes,
In Excel 2007 you need to do this:
-Bring up the developer tab: MS Office Orb -> Excel Options -> Popular -> Show the Developer Tab in the Ribbon
– Bring up the VBA editor: Developer -> Code -> Visual Basic or by just typing Alt+F11
– Enter design mode : Controls -> Design Mode
– Insert a “spin button”: Controls -> Insert -> ActiveX Controls -> Spin Button
– Right click button -> Properties -> change colors to your preference and change name to “UpDown” – or something you like
– Right click button -> Properties -> Change: Min=1, Max=10
Double click the button and you’ll see the following code in the VBA editor:
Private Sub UpDown_Change()
End Sub
– Now change the code to your preference for instance:
Private Sub UpDown_Change()
Range(“A1”) = UpDown.Value
End Sub
– After that don’t forget to exit design mode : Controls -> click Design Mode (before being able to use the button)
Best regards, George
Now I got it. I was so confused when I saw it at first… thanks to your explanation ı’m cool with it.
You are welcome!
Hi Menezes,
In Excel 2007 you need to do this:
-Bring up the developer tab: MS Office Orb -> Excel Options -> Popular -> Show the Developer Tab in the Ribbon
– Bring up the VBA editor: Developer -> Code -> Visual Basic or by just typing Alt+F11
– Enter design mode : Controls -> Design Mode
– Insert a “spin button”: Controls -> Insert -> ActiveX Controls -> Spin Button
– Right click button -> Properties -> change colors to your preference and change name to “UpDown” – or something you like
– Right click button -> Properties -> Change: Min=1, Max=10
Double click the button and you’ll see the following code in the VBA editor:
Private Sub UpDown_Change()
End Sub
– Now change the code to your preference for instance:
Private Sub UpDown_Change()
Range(“A1”) = UpDown.Value
End Sub
– After that don’t forget to exit design mode : Controls -> click Design Mode (before being able to use the button)
Best regards, George
vezi ca tu nu ai 301 redirect si-ti pierzi din ranking…nu am gasit un buton de contact, da-mi un email si-ti spun ce sa faci.
Bafta