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>