What time is it? Learn how to create a classic analog clock in Excel using VBA macros. We use shapes to create the frame and other components of the clock, including also the clock arrows or pointers. Then we rotate the arrows to indicate the current time, and keep updating the position by setting a loop or flow with VBA time functions. The clock arrows are lines grouped together with an oval shape to create the rotation of the classic analog clock in Excel.
Clock Layout
We use shapes to create the clock layout in Excel. The main frame is an oval shape with dimensions of a perfect circle. We can easily copy/paste that shape to create the other three circles we need to move the arrows of the clock (see later). We can add the shape manually or with VBA code; the latter ensures we create a perfect circle (see code below).
The macro adds a perfect circle to a position in the worksheet relative to the selected range. The width and height of the clock are the same and equal to the width of the selected range. We could also use specific values for each of those properties making sure the shape has the same width and height.
Then we add other components of the analog clock in Excel such as lines to represent the ticks for each hour or even minutes, text boxes for labeling the hours, or any other object to improve the design of the Excel analog clock.
Display Clock Arrows
We need to create a circle for each of the arrows of the analog clock in Excel. We could use the same macro we used before, or simply copy/paste the shape used as clock frame, although, the circle does not necessarily need to have the same size (but it needs to be a perfect circle). Then we add a line from the center of the circle upwards pointing to 12 o’clock. Let’s make the line thicker, as that’s representing the hour arrow of the Excel analog clock.
Now we group the line and the circle, and rename that grouped shape to “hour”. We can easily change that on the left side of the formula bar (just below the ribbon). Then we move the grouped shape to fit the clock frame created earlier.
We repeat the same process for the minutes and seconds clock arrows, but changing the length and the thickness of the arrow accordingly. We group and rename those as “minute” and “second”, and move them to fit the frame. In the end, they should all overlap pointing upwards as 12:00:00.
Set Current Time
We are going to move the arrows to indicate the current time by rotating the shapes a number of degrees relative to the number of hours, minutes, or seconds. For that, first we need to have the current hour, minute and second, which we get as indicated below.
Then we calculate the number of degrees we need to rotate each shape to point at the respective hour, minute, and second, in the Excel analog clock.
For minutes and seconds, that would be as simple as multiplying the current minute and second (minutenow and secondnow variables) by 6, which is 360 degrees divided by 60 (minutes or seconds) in one hour or minute, respectively.
For hours thought, we are going to add the percentage of minutes to the current hour in order to get a decimal number that allows to move the hour arrow in between the hour ticks and reflect the time more accurately. The new variable for hours (hourper) is a decimal or floating number and needs to be declared as a Single. Then we get the rotation (hourrot) with the last formula below.
Moving Clock Arrows
Those three variables (hourrot, minuterot, and secondrot) represent the number of degrees each shape needs to rotate to display the correct time in the Excel analog clock. We rotate each grouped shape (circle + line) as indicated below.
That will position the arrows pointing at the exact hour, minute, and second, i.e. the current time, as a frozen image. We need now to create a loop or flow similar to the one used with the digital clock in this other post (Simple Digital Clock Timer), in order to keep the clock running. We do that using VBA time functions such as the OnTime method of the Application object, and triggering the macro again after one second. That may look like the code below, where TimeInc should be declared as a Date, and MoveClock is another macro that calls back the first one.
We use another macro to have a condition to stop the clock through a Boolean variable declared at module level. However, we can have a different setup to control the status (on/off) of the Excel analog clock, and have it call the same macro after a second has elapsed. The following macros are used to implement a process to start/stop the analog clock in Excel.
The variable ClockOn needs to be declared at the module level and set to True when starting the clock in the first procedure (SetClock in this example). We can add some buttons to the worksheet to start or stop the analog clock in Excel. We could also start the clock when opening the workbook via the corresponding event procedure. But if we don’t have the stop button, the clock will run forever, even when attempting to close the file having some other Excel workbook open.
No comments:
Post a Comment