Creating a clock chart

Figure 18-19 shows an XY chart formatted to look like a clock. It not only looks like a clock, but it also functions as a clock. I can't think of a single reason why anyone would need to display a clock like this on a worksheet, but creating the workbook was challenging, and you might find it instructive.

nsnn start Clock stop Clcck 0 Analog Clock start Clock stop Clcck 0 Analog Clock

Clock Chart Excel
Figure 18-19: This clock is fully functional and is actually an XY chart in disguise.

This workbook is available on the companion CD-ROM.

Besides the clock chart, the workbook contains a text box that displays the time as a normal string, as shown in Figure 18-20. Normally this is hidden, but it can be displayed by deselecting the Analog Clock check box.

Set Text Box Digital Clock Vba
Figure 18-20: Displaying a digital clock in a worksheet is much easier but not as fun to create.

As you explore this workbook from the CD-ROM, here are a few things to keep in mind:

♦ The ChartObject is named ClockChart, and it covers up a range named DigitalClock, which is used to display the time digitally.

♦ The two buttons on the worksheet are from the Forms toolbar, and each has a macro assigned (StartClock and StopClock).

♦ The CheckBox control (named cbClockType) on the worksheet is from the Forms toolbar — not from the Control Toolbox toolbar. Clicking the object executes a procedure named cbClockType_Click, which simply toggles the Visible property of the ChartObject. When it's invisible, the digital clock is revealed.

♦ The chart is an XY chart with four Series objects. These series represent the hour hand, the minute hand, the second hand, and the 12 numbers.

♦ The UpdateClock procedure is executed when the Start Clock button is clicked. This procedure determines which clock is visible and performs the appropriate updating.

♦ The UpdateClock procedure uses the OnTime method of the Application object. This method lets you execute a procedure at a specific time. Before the UpdateClock procedure ends, it sets up a new OnTime event that will occur in one second. In other words, the UpdateClock procedure is called every second.

♦ The UpdateClock procedure uses some basic trigonometry to determine the angles at which to display the hands on the clock.

♦ Unlike most charts, this one does not use any worksheet ranges for its data. Rather, the values are calculated in VBA and transferred directly to the Values and XValues properties of the chart's Series object.

Although this clock is an interesting demo, it is not feasible to display a continually updating clock in a worksheet. The VBA macro must be running at all times in the background, and this would probably interfere with other macros and reduce the overall performance.

Was this article helpful?

+8 -5


Post a comment