ChartObjects.Count
varChart = GetChartInfo(ActiveSheet.ChartObjects(iChartIndex)
The ChartObjects.Count property will return the value of the last chart added. Then we
use that index to get the chart information.
As I noted at the end of the last example, we are going to let the user define the range to
chart by selecting the data for a particular product category.
Defining the Data Range and Legend Information
Before we modify the remaining code and its range references, let??™s add a few variables to hold
the range references from the user-defined selection.
1. Add the following variables:
Dim sDataRange As String
Dim sTitleRange As String
Dim sLegendRange As String
2. Since the user will select the data for us, we can remove the following line of code:
Range("A6:C9").Select
3. Put your cursor in the blank line created by removing the code in step 2, and add the
following code:
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
The Selection object (which is of the generic Object type) holds a Range object in this
case. Using the Range??™s Address and Cells properties, we can determine the address of the
entire range of the selection, the cell containing the title text (always the first cell in the data
CHAPTER 5 n CHARTING IN EXCEL 2007 218
range), and the range of cells containing the legend information (always column B for each
row in the selected range).
Pages:
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216