More Pie for Everyone

So we've created a pie chart and modified some of its properties to make the data displayed more meaningful. We've got quite a few categories on our Sales By Category worksheet. Can we use what we've learned and the code we've created to generate charts for the remaining categories? Of course!

Excel does not always place charts in the most appropriate place on a worksheet, so before we begin, let's be sure to move the Baked Goods & Mixes chart to the right of the data range on the Sales By Category worksheet by dragging and dropping it, as shown in Figure 5-28.

Figure 5-28. Chart moved next to data range

Next we'd like to chart the Beverages product category in a manner similar to the Baked Goods & Mixes pie chart. The simplest way to start is to copy the code from the MakePieChart macro we just recorded and modify it to use the data range A6:C9, which contains the Beverage category sales information.

1. If it's not already open, open the VBE by going to the Developer ribbon and selecting Code > Visual Basic, or bypressing Alt+F11.

2. If it's not already open, open Standard Module1.

3. Copy the MakePieChart macro.

4. Paste the copy below MakePieChart and rename it MakePieChart2.

5. Modify all range references to refer to the data range containing the Beverage category sales information, as shown in Listing 5-2.

Listing 5-2. MakePieChart2 Subroutine Modified to Chart the Beverage Category

Sub MakePieChart2()

Range("A6:C9").Select ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range("'Sales By Category'!$A$6:$C$9") 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

As in our original example, we are selecting a range of data (A6:C9), and then adding a chart and setting the source data range to the selected range. Then we set the chart type to Pie (xlPie) and set the name and legend values.

6. Run the MakePieChart2 macro.

As shown in Figure 5-29, Excel still insists on placing the pie chart on top of our data range. In fact, if we had not moved the Baked Goods & Mixes pie chart, the new chart would be sitting on top of it (and it still is partially covering our existing chart)!

Figure 5-29. Excel places any new chart on our data range.

Not to worry. You'll recall that when we created our first chart using the Macro Recorder, Excel used the AddChart method to insert the chart. We looked at the optional arguments for that method in Table 5-1. 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.

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.

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")

ActiveChart.ChartType = xlPie

ActiveChart.SeriesCollection(l).Name = "='Sales By Category'!$A$6" ActiveChart.SeriesCollection(l).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.

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.

0 0

Post a comment