Secondplotsize Excel

If only two data labels overwrite each other, rotating the angle of the first pie slice may solve the problem. Use the FirstSliceAngle property to rotate the pie. However, this exposes one of the most frustrating aspects of creating charts with VBA. If you are creating a chart in the user interface, you can tell whether labels are overlapping each other and know to adjust the FirstAngleSlice. There is no method or property in VBA that alerts you that your chart looks bad because of overlapping labels. I've found that using a FirstSliceAngle to rotate the chart 80-100° often gives the most room for the smaller pie slices:

Sub RotateFirstSlice() Dim Cht As Chart

Set Cht = Worksheets("Pie").ChartObjects(1).Chart ActiveChart.PieGroups(1).FirstSliceAngle = 100 End Sub

If many slices overwrite each other, then the solution might lie in taking all the slices under 5% of the total, grouping them into a single slice, and showing the detail from this slice in a bar chart. This can all be accomplished with VBA code:

Sub CreateBarOfPie() Dim Cht As Chart Dim CG As ChartGroup

Set Cht = Worksheets("Pie").ChartObjects(1).Chart Cht.ChartType = xlBarOfPie Set CG = Cht.PieGroups(l) With CG

.SplitType = xlSplitByPercentValue .SplitValue = 5 ' smaller than 5% combined .GapWidth = 200 1 gap between pie and bars .SecondPlotSize = 55 ' % relative to pie size End With End Sub

The resulting chart is shown in Figure 10.19.

Figure 10.19

Using the xlBarOfPie chart type allows the smaller categories to be combined and exploded into a separate bar.This usually alleviates the problems with overlapping labels.

Figure 10.19

Using the xlBarOfPie chart type allows the smaller categories to be combined and exploded into a separate bar.This usually alleviates the problems with overlapping labels.

0 0

Post a comment