Listing 5-6. The Completed PlaceChartDynamic Subroutine
Sub PlaceChartDynamic()
Dim spacer As Integer
Dim varChartInfo As Variant
Dim iChartIndex As Integer
Dim sDataRange As String
Dim sTitleRange As String
Dim sLegendRange As String
iChartIndex = ActiveSheet.ChartObjects.Count
varChartInfo = GetChartInfo(ActiveSheet.ChartObjects(iChartIndex))
spacer = 25
sDataRange = Selection.Address
sTitleRange = Selection.Cells(1, 1).Address
sLegendRange = Selection.Cells(1, 2).Address & ":" ??
& Selection.Cells(1, 2).Offset(Selection.Rows.Count - 1).Address
ActiveSheet.Shapes.AddChart(, varChartInfo(2), _
(varChartInfo(1) + varChartInfo(3) + spacer)) ??
.Select
ActiveChart.SetSourceData Source:=Range("'Sales By Category'!" & sDataRange)
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection(1).Name = "='Sales By Category'!" & sTitleRange
ActiveChart.SeriesCollection(1).XValues = "='Sales By Category'!" ??
& sLegendRange
End Sub
Testing the Code
Now that we??™ve got our code rewritten to be much more flexible, let??™s select a data range and
create a formatted chart placed below the last chart on the Sales By Category worksheet.
1. Select the data for the Candy product line (cells A10:C13), as shown in Figure 5-31.
Figure 5-31. Data selected for dynamic charting
CHAPTER 5 n CHARTING IN EXCEL 2007 220
2. Run PlaceChartDynamic by going to the Developer ribbon and choosing Code ?¤
Macros.
3. The new chart is inserted below the Beverages chart and is aligned to its left side, as
shown in Figure 5-32.
Pages:
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218