These optional arguments include the type of chart and its top, left,
width, and height settings (in pixels). We can use these optional arguments to place the new
chart immediately below the existing chart, and align it with it as well.
1. Delete the Beverages pie chart from the Sales By Category worksheet.
a. Select the chart by clicking it on its borders.
b. Press the Delete key on your keyboard.
2. On Standard Module1, create a new subroutine and name it PlaceChart.
3. Add the following variable declarations to the PlaceChart subroutine:
Dim arrChartInfo(3) As Variant
Dim spacer As Integer
The arrChartInfo(3) variable will hold an array that contains information about the
existing chart (Chart 1), such as its name and top, left, and height values. We??™ll use the
spacer variable to place some empty space between our charts.
CHAPTER 5 n CHARTING IN EXCEL 2007 213
4. Add the following code after the variable declarations:
With ActiveSheet.ChartObjects(1)
arrChartInfo(0) = .Name
arrChartInfo(1) = .Top
arrChartInfo(2) = .Left
arrChartInfo(3) = .Height
End With
spacer = 25
Within the With...End With block, we are setting the array elements equal to the Name,
Top, Left, and Height properties of the ChartObjects(1) item, which is of course the
existing (and only) chart on the worksheet at the moment. We could also have referred
to the chart by name, as in ActiveSheet.ChartObjects("Chart 1").
For this example, we??™re setting the spacer variable to a value of 25, but you can use any
value that suits your purpose.
Pages:
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211