In our next example, we are going to give the user the ability to select a range of data to be
charted. In addition, we??™ll make the placement of the chart more dynamic as well. In our last
example, we knew we wanted to refer to the first chart on the worksheet. Now that we??™ve got
more than one chart on the worksheet, we??™ll need to grab the location of the last chart inserted
and place our new chart below that.
CHAPTER 5 n CHARTING IN EXCEL 2007 215
Dynamically Placing a Chart
In the VBE, on Standard Module1, create a new subroutine called PlaceChartDynamic. Copy the
code from the PlaceChart procedure and paste it into PlaceChartDynamic.
Before we begin our exercise, we??™re going to move the opening lines of code into their own
function. This subroutine begins by getting location information about the chart we want to
use as a placement reference, but is not directly involved in creating a chart. It??™s always a good
idea from a maintenance perspective to keep our functional operations separate, so we are
going to create a function that returns the location of a chart in an array.
Storing Chart Location in an Array
1. On Standard Module1, create a new function named GetChartInfo().
2. Add the following argument to the function:
Private Function GetChartInfo(MyChart As ChartObject) As Variant
The argument MyChart will pass a ChartObject into our function. From this object, we??™ll
return the location information in GetChartInfo.
3. Add a variable: Dim varReturn As Variant.
Pages:
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213