4. Moving to the next line of code, we are going to replace all references to the
arrChartInfo array that we are no longer using with a reference to the return value of
the GetChartInfo function, varChartInfo.
ActiveSheet.Shapes.AddChart(, arrChartInfo(2), ??
(arrChartInfo(1) + arrChartInfo(3) + spacer)) ??
.Select
When finished, the line of code that adds and places the new chart will look like this:
ActiveSheet.Shapes.AddChart(, varChartInfo(2), ??
(varChartInfo(1) + varChartInfo(3) + spacer)) ??
.Select
Setting the Data Range and Legend Information
Now we??™ll modify the line of code that sets the chart??™s data range.
1. Put your cursor on this line of code:
ActiveChart.SetSourceData Source:=Range("'Sales By Category'!$A$6:$C$9")
2. Modify it to read as follows:
ActiveChart.SetSourceData Source:=Range("'Sales By Category'!" & sDataRange)
3. Leave the next line of code as is:
ActiveChart.ChartType = xlPie
4. Now we??™ll set the title range. Put your cursor on this line of code:
ActiveChart.SeriesCollection(1).Name = "='Sales By Category'!$A$6"
5. Modify it to read as follows:
ActiveChart.SeriesCollection(1).Name = "='Sales By Category'!" & sTitleRange
6. All that??™s left is to set the legend text data range. Put your cursor on the last line of code:
ActiveChart.SeriesCollection(1).Name = "='Sales By Category'!" & sTitleRange
7. Modify it to read as follows:
ActiveChart.SeriesCollection(1).XValues = "='Sales By Category'!" ??
& sLegendRange
CHAPTER 5 n CHARTING IN EXCEL 2007 219
The completed subroutine should now look like Listing 5-6.
Pages:
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217