Microsoft Office Tutorials and References
In Depth Information
Discovering VBA Charting Tricks
Creating unlinked charts
Normally, an Excel chart uses data stored in a range. Change the data in the range, and the chart
is updated automatically. In some cases, you might want to unlink the chart from its data ranges
and produce a dead chart (a chart that never changes). For example, if you plot data generated
by various what-if scenarios, you might want to save a chart that represents some baseline so
that you can compare it with other scenarios.
The three ways to create such a chart are
h Copy the chart as a picture. Activate the chart and choose Home➜Clipboard➜Copy➜
Copy As Picture (accept the defaults in the Copy Picture dialog box). Then click a cell and
choose Home➜Clipboard➜Paste. The result will be a picture of the copied chart.
h Convert the range references to arrays. Click a chart series and then click the formula
bar. Press F9 to convert the ranges to an array, and press Enter. Repeat this for each
series in the chart.
h Use VBA to assign an array rather than a range to the XValues or Values properties
of the Series object. This technique is described next.
The following procedure creates a chart (see Figure 18-17) by using arrays. The data isn’t stored
in the worksheet. As you can see, the SERIES formula contains arrays and not range references.
Dim MyChart As Chart
Set MyChart = ActiveSheet.Shapes.AddChart.Chart
.SeriesCollection(1).Name = “Sales”
.SeriesCollection(1).XValues = Array(“Jan”, “Feb”, “Mar”)
.SeriesCollection(1).Values = Array(125, 165, 189)
.ChartType = xlColumnClustered
Because Excel imposes a limit to the length of a chart’s SERIES formula, this technique works
only for relatively small data sets.