Select
ActiveSheet.Shapes.AddChart(, arrChartInfo(2), ??
(arrChartInfo(1) + arrChartInfo(3) + spacer)) ??
.Select
ActiveChart.SetSourceData Source:=Range("'Sales By Category'!$A$6:$C$9")
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection(1).Name = "='Sales By Category'!$A$6"
ActiveChart.SeriesCollection(1).XValues = "='Sales By Category'!$B$6:$B$9"
End Sub
Add the following variable declarations to PlaceChartDynamic:
Dim varChartInfo As Variant
Dim iChartIndex As Integer
The varChartInfo variable will hold the array returned from the GetChartInfo function
and will be used to place our new chart. iChartIndex will hold the index value of the last chart
added to the worksheet.
CHAPTER 5 n CHARTING IN EXCEL 2007 217
Let??™s take a look at the remaining code in the PlaceChartDynamic procedure to get an idea
of the changes we??™ll make to make this routine much more flexible.
We see numerous hard-coded range references. Our new code will have to
??? Find the last chart added and use its coordinates to insert the new chart below it
??? Define the data range for the chart
??? Define the cell that contains the name of the product category to display in the chart
title
??? Define the range containing the month values for the chart legend
Let??™s attack these one at time. First, let??™s get the chart location information from the
GetChartInfo function.
Getting the Coordinates from the Existing Chart
Add the following lines of code:
iChartIndex = ActiveSheet.
Pages:
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215