Chart Manipulation

Usually any programmatic manipulation with a chart is relatively minor. Your needs may be totally different than anything I've ever done, but when I have had applications that use charts in the past, usually the charts are precreated and then displayed or hidden as needed. You can manage which data is displayed in a chart by having a static chart source range and then programmatically managing which data appears in the chart source range. In this way, you can avoid all of the details of having to create a chart on the fly.

Why do you want to avoid creating charts on the fly? Although it is easy to create a simple chart on the fly, it is difficult to create a nice chart on the fly. If you've ever had to create a series of presentation quality charts, think about how many revisions you went through before you finally had a chart that looked good. The fact is, because charts are such a visual medium, you can't really go about developing the code to create a chart without first developing the chart manually to make sure you're happy with the end product. Because you have to do this anyway and because it's such a trivial task to modify many aspects of the chart once it is created, I think you'll find that usually your chart-related programming tasks will be involved with manipulating an existing chart.

A lot of chart-related objects exist. One of the first orders of business is to link these various chart objects to the actual chart elements that they represent. That is 90 percent of the battle. To do this, begin by studying a picture of a chart like the one in Figure 10.14.

As long as you know the "official" names of all of the elements that make up a chart, finding the VBA counterpart to the chart element is pretty easy. In most instances, the VBA object is named the same thing with the spaces removed (i.e., Plot Area becomes PlotArea). You can see for yourself by taking a look at the chart object model shown in Figure 10.15.

Figure 10.14

Elements of a chart

Figure 10.14

Elements of a chart

Figure 10.15

The chart object model

Figure 10.15

The chart object model

0 0

Post a comment