Creating a Digital Clock #2 – sprite driver, timing macro and functions, full model

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,

George

[sociallocker][/sociallocker]

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>

50 Comments

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

  2. Hi! with your help, i get things done. thank u. Keep up!!!!

  3. Virgile, Right click the button and see which macro is attached to the button. Also just open the VBA editor and poke troug the macros, that macro must have a similar name (or the same) with the button.
    It must be a “count up” type of macro.

  4. Hi! may be i’m not expressing well what is my prob. in your digital clock(excel, sheet Digital Clock_2), i found no spin button needed to be pushed to display the digit, they seem to be displayed automatically. the only thing the user do is to press on Run-Pause, and digit get displayed according to time. How to do exactly the same?

  5. Watch wat the spin buttn does and the if() functions hat change the coordinates of the sprites. All stay out but only one sprite comes in the view.

  6. Thank u. what i don’t know exactly, is the way to bring them up at different moments in time. in tutorial, the example given does not explain it well: the user press Spin button, to display one digit. how to make it so that digit (sprites) display itself when time change? i mean to have exactly the same digital clock as the one you designed.

  7. Sprites don’t change, you have an army of sprites of different shapes and sizes and bring them up at different moments in time, one at a time. During this time you keep the rest hidden.

  8. Hi! i followed your tutorial and appreciate your work but i still not understand how sprites will be changing as the time change i.e to connect the sprites generated with time. Please help me to solve that issue!

  9. Hi Greg, sorry for the late reply, insert the statement “DoEvents” liberally within your main macro when things don’t update or the program is kind of non responsive. Look in my code and add more line with this statement (DoEvents). Experiment adding this statement every other line and then start removing most of them while making sure the program maintains its functionality.

  10. George this was an awesome resource. I was able to design the clock. My problem is that when I click the Start/Stop button my clock won’t stop running.

    I can open the file, enable the macros, and start the clock. But when I click stop, the clock continues to tick and then Excel crashes! Any suggestions on what could be going wrong? I double checked the code and it matches yours.

    Aside from that your info was very helpful!

  11. Hassan, Make sure you use “DoEvents” as often as you can in your macro. Look at my code and see that I use “DoEvents” liberally. In 2007 or 2010 you need to use it even more often to allow for chart refreshing than in 2003.

  12. I MADE IT 🙂 thanks anyways 😛

  13. i somehow cannot associate the button to the table 🙁

  14. George it was a pleasure going through your website.

    i am stuck. need help. i am looking for a 3 or 4 digit display. i have been able to create the button, assign macro to it. but the changes in the table are not appearing. how do you “Verify the functionality of the button-macro pair.” ???

    regards
    Hassan

  15. i send you an invite

  16. I hate those chats. I prefer Skype or phone. My handle is: ge-lun and my # is 719-373-0680.

  17. do you got anything like msn or something so we could chat?

  18. No problem, you are welcome. Take everything on this site as a tease. Be original/creative, the math and the syntax should be secondary.

  19. george thanks you brought me a new idea thanks oooh i hope we could keep chatting with some site so you can help me if i need i hope that you want that

  20. I was a lazy student myself when help was close. Try to get a few ideas from the blog and do something new and much better yourself (not a clock, the clock is just a teaser or a starter). Make some sort of shooting game or some strategy game, very simple at first and add someting every day to it till it becomes very cool. You know how to introduce sprites, move them around and hide them. It’s all there (check other models too). Good luck!

  21. i am now at page 10 🙂 thanks for the help
    page 10 takes looooooooooong

  22. I am not sure what you mean. Try to be more thorugh.
    My impression is that you need a nanny. You need to spend some time with this. If you proceed like in school (get help from colleagues or the teacher every time you get stuck) you won’t ever be independent.

  23. ok i got that but when i am on page 7 and i place the codes on the good places and stuff then my digit goes away and when i try to fix it again then it don’t work only if i go to 0 then i got the digit 9 and only that ones working

  24. can i just copy the digit then place it on the dot on the middle of the chart area then i’m done?

  25. And of course you can replace the other 2 points with two other shapes by using the same recipe. After that you can use VBA to change the coordinates and create some sort of “puppet show” where all the points except one is in the range of the axes (visible) and the restare far away to the left for instance(say -999,0). We call these shapes (or puppets) sprites. You can move within the chart window or you can send far away hence they become invisible.

Leave a Reply

Your email address will not be published. Required fields are marked *