5. Press Enter twice to insert blank lines in the code after spacer = 25.
6. Copy the code from the MakePieChart2 macro and paste it after the blank lines.
The completed PlaceChart subroutine should look like Listing 5-3.
Listing 5-3. The Completed PlaceChart Subroutine
Sub PlaceChart()
Dim arrChartInfo(3) As Variant
Dim spacer As Integer
With ActiveSheet.ChartObjects(1)
arrChartInfo(0) = .Name
arrChartInfo(1) = .Top
arrChartInfo(2) = .Left
arrChartInfo(3) = .Height
End With
spacer = 25
'
' The following code is from MakePieChart2 Macro
'
Range("A6:C9").Select
ActiveSheet.Shapes.AddChart(, arrChartInfo(2), ??
(arrChartInfo(1) + arrChartInfo(3) + spacer)) ??
.Select
ActiveChart.SetSourceData Source:=Range("'Sales By Category'!$A$6:$C$9")
CHAPTER 5 n CHARTING IN EXCEL 2007 214
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
7. Return to the Sales By Category worksheet and run the PlaceChart procedure.
Figure 5-30 shows the result of our placement efforts.
Figure 5-30. Beverages chart aligned with Baked Goods & Mixes chart
Fantastic! We modified the original MakePieChart code by changing the range references
and referring to the location of the original chart to determine where to put the new chart. But
let??™s make this code a bit more dynamic. Our users aren??™t going to give us ranges of data to
chart, they most likely will want to create them on the fly as needed.
Pages:
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212