In This Chapter
Some amusing and recreational aspects of charting
Animated charts using VBA
Although Excel is used primarily for serious applications, many users discover that this product has a lighter side. This is especially apparent in the area of charts and graphics. Although the topics discussed here deal with nonserious applications of graphics in Excel, you'll quite possibly discover some techniques that you can apply to your more serious charting efforts.
Note
All of the examples in this chapter are on the companion CD–ROM, and many of these examples use macros. I don't discuss the programming aspects in this chapter, but the VBA projects are all unprotected, so a password is not needed if you'd like to view and experiment with the code. Depending on your settings, you may receive a security warning when the workbook is opened. Be assured that these files are virus–free.
When people think of animation software, Excel certainly isn't the first application that comes to mind. But, with the aid of some relatively simple macros, you can coax some crude animations out of Excel.
Note
If you're a VBA programmer, be aware that the "secret" to producing animations in Excel is to use the following VBA statement within a loop:
DoEvents
This statement causes a refresh of the screen. Without this statement, the results of your animation code are not displayed until the macro ends — which pretty much defeats the purpose of animation! Also, I've found that animations that work fine in previous versions of Excel often require an additional DoEvents statement in Excel 2007.
Chapter 6 covers the wonderful world of shapes. If you've played around with shapes, you may enjoy seeing them in action. Create an AutoShape, add a touch of 3–D formatting, and toss in a VBA macro. You have a recipe for a simple animation.
The practical applications are limited or maybe even nonexistent. But most people are amazed to discover that you can do this sort of thing in Excel, and it's a good way to take a break from number crunching.
Note
The examples in this section are available on the companion CD–ROM. The filenames are bouncing shapes.xlsm and rotating shape.xlsm.
Figure 13-1 shows three shapes that bounce around inside of a cell. The code is written such that you can add more shapes. The speed of the bouncing depends on your processor speed and the complexity of the shapes. For example, if you use a gradient fill, 3–D formatting, or a shadow, the action takes place at a much slower speed because Excel must do additional calculations before rendering the shapes.
The shape in the worksheet in Figure 13-2 rotates. You select which axes to rotate by using the check boxes. Notice that the text rotates along with the shape, and the text has depth. As the shape rotates, the depth of the text becomes apparent.
When you get tired of watching the animated shapes, turn your attention to animated charts. A relatively simple macro can convert a chart into an action–packed piece of entertainment. The macros in these examples increment the value in a cell. This cell is then used in formulas that are displayed in the chart.
Figure 13-3 shows an example of an animated chart. This is a 3–D line chart. When animated, the effect is reminiscent of bird wings in flight.
Note
The companion CD–ROM includes this animated chart, plus several other examples. The filename is animated charts.xlsm.
Round and round it goes. Where it stops, nobody knows.
Figure 13-4 shows a doughnut chart with 12 data points, set up like a carnival wheel of fortune. The numbers are data labels, and the slices were formatted individually to get the alternating color effect.
Click the button to kick off a macro that systematically changes the angle of the first slice, which results in a rotating chart. The difficult Part was programming the macro so that the spinning gradually slows before the wheel comes to a stop.
Note
This example, named doughnut chart spinner.xlsm, is available on the companion CD–ROM.
Charts that plot data generated by trigonometric functions can be stunning. Even if you don't know the difference between a SIN function and a stop sign, you can still create some incredible designs.
I start with a simple example. The scatter chart in Figure 13-5 plots the data in column B against the data in column C (the chart axes are hidden). Column A contains formulas that generate a sequence of numbers, using the increment value in cell A1.
Note
This example, named plot sin and cosine.xlsx, is available on the companion CD–ROM.
The formula in B3, which is copied to 99 cells below it, is as follows:
=SIN(A3)
The formula in C3, which is also copied to the cells below, is as follows:
=COS(A3)
The chart looks dramatically different with various increment values in cell A1. Figure 13-6 shows the chart when cell A1 contains 4.2. To display various geometric shapes, use a formula in the form of the following, varying the value of n. For example, when n is 4, the chart displays an octagon.
=PI()/n
Figure 13-7 shows a scatter chart that displays "hypocycloid" curves. A hypocycloid curve is defined as follows:
The curve produced by fixed point P on the circumference of a small circle of radius b rolling around the inside of a large circle of radius a > b
In other words, this type of curve is the same as that generated by Hasbro's popular Spirograph toy, which you may remember from your childhood.
The formulas that generate the data used in the series are rather complex, but they use three parameters, stored in E1:E3. Change any of these parameters, and you get a completely different design. I guarantee that you will be amazed by the variety of charts that you can generate — some of them are simply stunning. Figure 13-8 shows a few more examples.
Note
The companion CD–ROM contains two versions of this file. The first, named hypocycloid chart.xlsx, enables you to change the parameters manually. A more sophisticated version (hypocycloid chart – animated.xlsm) uses macros to randomly generate parameter values and even has an animation option.
The chart in Figure 13-9 is a radar chart with three series. The chart has 360 axes, which represent the degrees in a circle. The axes are hidden. If they were visible, they would completely overwhelm the chart.
Data for the three series is generated by formulas in columns B:D. These formulas use trigonometric functions and depend on the values in column A and the three adjustment parameters in B1:B3. These cells are linked to Scroll Bar controls. Manipulating the scroll bars results in many variations on the design.
Note
This workbook, named radar chart designs.xlsx, is available on the companion CD–ROM.
Sometimes a chart can resemble a picture. This section presents two examples of chart art (and I use the term art loosely).
One day I was working with an area chart, and it occurred to me that the chart resembled a mountain range. I quickly abandoned my original task and set out to create the ultimate mountain range chart. The result is shown in Figure 13-10 (it looks better in color). Okay, I cheated. The moon and stars are actually shapes.
Note
This workbook, named mountain ranges.xlsx, is available on the companion CD–ROM.
Work with bubble charts long enough and you may start seeing faces take shape. Figure 13-11 shows a cartoon–like mouse face made up of a data series with nine data points. The data in columns B and C position the data points, and the values in column D control the size of the bubbles. Each bubble was formatted separately to control the color and gradient effects.
The folks at Pixar Animation Studios have nothing to worry about.
Note
This workbook, named bubble chart mouse.xlsx, is available on the companion CD–ROM.
The chart shown in Figure 13-12 displays a frown or a smile (and expressions in between) based on the value in cell D1. A value of −1 results in an unhappy face, and a value of +1 displays a very happy face. A value of 0 shows a neutral face.
Actually, only the mouth is a chart (an XY chart). The other facial parts are shapes. The mouth shape is determined by 24 formulas that use the SIN function.
Because a scatter chart can connect dots displayed at any XY coordinate, this type of chart has lots of fun potential. Figure 13-13 shows a simple example: an arrow drawn with eight connected data points.
Note
The examples in this section are available on the companion CD–ROM. The filename is scatter chart drawings.xlsx.
Note
Previous versions of Excel allowed you to click a data point on a chart and drag it to a new location. This action also modified the underlying data for the chart. That feature, which made it very easy to create scatter chart drawings, was removed from Excel 2007.
Figure 13-14 shows a scatter chart that displays a geometric design generated by two columns of formulas. Figure 13-15 shows the same chart after applying some randomness, glow, shadow, and a smoothed line.
Note
In the previous edition of this book, I included a few examples of maps created with a scatter chart. For example, I created a detailed outline map of California that uses more than 3, 000 data points. Unfortunately, Excel 2007 cannot handle such complex charts. Performance slows to an unacceptable level — or else Excel simply stops responding.
Figure 13-16 shows a scatter chart formatted to look like a clock. It not only looks like a clock but also functions like one. There is really no reason that anyone would need to display a clock such as this on a worksheet, but creating the workbook was challenging, and you may find it instructive.
The chart uses three data series for the clock hands: one for the hour hand, one for the minute hand, and one for the second hand. These series contain formulas that use Excel's NOW function (which returns the current time). The formulas use trigonometric functions to determine the angle of the hands for the time of day. A simple macro is executed at onesecond intervals. This macro simply calculates the sheet, which updates the formulas and the clock.
The chart uses another series to display the numbers. This data series draws a circle with 12 data points. The numbers consist of manually entered data labels.
Deselect the Analog Clock check box to reveal a hidden digital clock (see Figure 13-17). This clock consists of 28 merged cells that contain a simple formula:
=NOW()
Note
The example in this section, named analog clock chart.xlsx, is available on the companion CD–ROM.
The workbook shown in Figure 13-18 simulates rolling two dice. The outcome of each roll is stored in a range, which is displayed in a chart. The chart shows the actual distribution of the dice rolls, as well as the theoretical distribution of throwing two dice. This workbook may be useful for teaching elementary probability theory.
Note
This example, named dice roller.xlsm, is available on the companion CD–ROM.
Following are a few points to keep in mind while you examine this workbook:
A simple VBA macro, triggered by the Roll 'em button, is used to store the history of the dice rolls in columns A:D. Another macro, which deletes the history, is attached to the Clear History button.
The dice picture uses no graphics. The graphics are generated by IF functions that determine whether a particular dot should be visible, based on the randomly generated dice value. The dot is actually a Wingdings font character.
The chart series that displays the theoretical distribution uses an array, not a range. Because the series never changes, you don't need to store the values in a range.
A text box in the chart displays the number of dice rolls. This text box is linked to a cell that determines the number of items in the History area of the worksheet.
Nothing's more exciting than a horse race at the office. Figure 13-19 show a scatter chart that contains four data points. Each data point was replaced by a clip art image of a horse. Click the Run button, and a random horse moves forward. Just keep clicking until a horse wins. Come to think of it, changing the toner cartridge in the printer might be more fun than this.
Note
This example, named horse race.xlsm, is available on the companion CD–ROM.
Microsoft Office 2007 includes tons of clip art that many people never bother to use. Figure 13-20 shows two examples of using clip art to augment a chart. Your boss might not appreciate the humor.
Note
The examples in this section are available on the companion CD–ROM. The filename is clip art with charts.xlsx.
Figure 13-21 shows a few other examples of combining clip art with a chart.
