Formatting pie charts via VBA

[email protected] · 06-13-2006, 11:15 AM

Hi,

I'm currently struggling to change the Title and the Legend of pie
charts that are generated by passing data from Word to Excel, then
copying the chart over to Word. (I'm using Word + Excel 2000).

The VBA is use works OK, and generates a reasonable looking chart.

However, no matter what I try, I cannot alter the chart title, or the
legend. Let me expand on this a little.

1. The Chart Title is always centered. However, the legend (if there a
lot of entries in the chart) spills over the title and obscures it.
Therefore, I need to position the title on the far left of the chart.

2. If I have more than, say, eight elements in the chart, the legend
spills beyond the bottom of the chart, but there seems to be huge
spacing gaps between each entry on the legend.
However, if I create a chart using an Excel Range and the Chart Wizard,
it seems to easily accomodate ten or more entries, and space them nice
and evenly.

It does not seem to matter whether I set "Legend.AutoScaleFont" to
true or false, or whether I try and position the title. It always shows
up the exactly the same.

I thought I had cracked this, but I obviously need to do some fine
tweaking. Any suggestions appreciated.

Code below:

//// cNumRows and cNumCols are variables used to populate an Excel
Range from a Word VBA array of data

Set oChart = oSheet.ChartObjects.Add.Chart
oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
cNumCols), PlotBy:= _
xlColumns
oChart.ChartType = xl3DPieExploded
oChart.RightAngleAxes = True
oChart.PlotArea.Height = 215
oChart.PlotArea.Width = 215
oChart.PlotArea.left = 5
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Border.LineStyle = -4142
oChart.Elevation = 30
oChart.Rotation = 80
oChart.Pie3DGroup.VaryByCategories = True
oChart.HasTitle = True
oChart.ChartTitle.Top = 0
oChart.ChartTitle.left = 0
oChart.ChartTitle.Characters.Text = "Current Asset
Allocation"
oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=True
oChart.SeriesCollection(1).DataLabels.Font.Size = 8

oChart.HasLegend = True
With oChart.Legend
.Legend.Shadow = True
'.Legend.Position = xlLegendPositionRight
.Legend.AutoScaleFont = False
.Legend.Font.Size = 3
End With

Thanks
Neil.

Andy Pope · 06-13-2006, 11:55 AM

Hi,

Your chart title positioning code works for me in a test environment.
The legend part I had to tweak slightly.

oChart.HasLegend = True
With oChart.Legend
.Shadow = True
.Position = xlLegendPositionRight
.AutoScaleFont = False
.Font.Size = 3
End With

Does your code raise an error (assuming no error suppression is on) or
does it just not work?

Cheers
Andy

[email protected] wrote:
> Hi,
>
> I'm currently struggling to change the Title and the Legend of pie
> charts that are generated by passing data from Word to Excel, then
> copying the chart over to Word. (I'm using Word + Excel 2000).
>
> The VBA is use works OK, and generates a reasonable looking chart.
>
> However, no matter what I try, I cannot alter the chart title, or the
> legend. Let me expand on this a little.
>
> 1. The Chart Title is always centered. However, the legend (if there a
> lot of entries in the chart) spills over the title and obscures it.
> Therefore, I need to position the title on the far left of the chart.
>
> 2. If I have more than, say, eight elements in the chart, the legend
> spills beyond the bottom of the chart, but there seems to be huge
> spacing gaps between each entry on the legend.
> However, if I create a chart using an Excel Range and the Chart Wizard,
> it seems to easily accomodate ten or more entries, and space them nice
> and evenly.
>
> It does not seem to matter whether I set "Legend.AutoScaleFont" to
> true or false, or whether I try and position the title. It always shows
> up the exactly the same.
>
>
> I thought I had cracked this, but I obviously need to do some fine
> tweaking. Any suggestions appreciated.
>
>
>
>
> Code below:
>
> //// cNumRows and cNumCols are variables used to populate an Excel
> Range from a Word VBA array of data
>
> Set oChart = oSheet.ChartObjects.Add.Chart
> oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
> cNumCols), PlotBy:= _
> xlColumns
> oChart.ChartType = xl3DPieExploded
> oChart.RightAngleAxes = True
> oChart.PlotArea.Height = 215
> oChart.PlotArea.Width = 215
> oChart.PlotArea.left = 5
> oChart.PlotArea.Fill.Visible = False
> oChart.PlotArea.Fill.Visible = False
> oChart.PlotArea.Border.LineStyle = -4142
> oChart.Elevation = 30
> oChart.Rotation = 80
> oChart.Pie3DGroup.VaryByCategories = True
> oChart.HasTitle = True
> oChart.ChartTitle.Top = 0
> oChart.ChartTitle.left = 0
> oChart.ChartTitle.Characters.Text = "Current Asset
> Allocation"
> oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
> LegendKey:=False _
> , HasLeaderLines:=True
> oChart.SeriesCollection(1).DataLabels.Font.Size = 8
>
> oChart.HasLegend = True
> With oChart.Legend
> .Legend.Shadow = True
> '.Legend.Position = xlLegendPositionRight
> .Legend.AutoScaleFont = False
> .Legend.Font.Size = 3
> End With
>
>
>
> Thanks
> Neil.
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[email protected] · 06-14-2006, 03:50 AM

> Does your code raise an error (assuming no error suppression is on) or
> does it just not work?

It simply does not work. No error messages are displayed.

Again, when I tried it with your tweaked code, it made no difference.
In fact if I set the font size to 30 instead of 3, it makes no
difference. The legend remains exactly the same.

Having looked through the documentation, most of the code seems
correct, but Excel is not co-operating.

The version of Word/Excel I am using is 9.02720 if this makes any
difference.

Thanks
Neil.

Andy Pope wrote:
> Hi,
>
> Your chart title positioning code works for me in a test environment.
> The legend part I had to tweak slightly.
>
> oChart.HasLegend = True
> With oChart.Legend
> .Shadow = True
> .Position = xlLegendPositionRight
> .AutoScaleFont = False
> .Font.Size = 3
> End With
>
> Does your code raise an error (assuming no error suppression is on) or
> does it just not work?
>
> Cheers
> Andy
>
> [email protected] wrote:
> > Hi,
> >
> > I'm currently struggling to change the Title and the Legend of pie
> > charts that are generated by passing data from Word to Excel, then
> > copying the chart over to Word. (I'm using Word + Excel 2000).
> >
> > The VBA is use works OK, and generates a reasonable looking chart.
> >
> > However, no matter what I try, I cannot alter the chart title, or the
> > legend. Let me expand on this a little.
> >
> > 1. The Chart Title is always centered. However, the legend (if there a
> > lot of entries in the chart) spills over the title and obscures it.
> > Therefore, I need to position the title on the far left of the chart.
> >
> > 2. If I have more than, say, eight elements in the chart, the legend
> > spills beyond the bottom of the chart, but there seems to be huge
> > spacing gaps between each entry on the legend.
> > However, if I create a chart using an Excel Range and the Chart Wizard,
> > it seems to easily accomodate ten or more entries, and space them nice
> > and evenly.
> >
> > It does not seem to matter whether I set "Legend.AutoScaleFont" to
> > true or false, or whether I try and position the title. It always shows
> > up the exactly the same.
> >
> >
> > I thought I had cracked this, but I obviously need to do some fine
> > tweaking. Any suggestions appreciated.
> >
> >
> >
> >
> > Code below:
> >
> > //// cNumRows and cNumCols are variables used to populate an Excel
> > Range from a Word VBA array of data
> >
> > Set oChart = oSheet.ChartObjects.Add.Chart
> > oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
> > cNumCols), PlotBy:= _
> > xlColumns
> > oChart.ChartType = xl3DPieExploded
> > oChart.RightAngleAxes = True
> > oChart.PlotArea.Height = 215
> > oChart.PlotArea.Width = 215
> > oChart.PlotArea.left = 5
> > oChart.PlotArea.Fill.Visible = False
> > oChart.PlotArea.Fill.Visible = False
> > oChart.PlotArea.Border.LineStyle = -4142
> > oChart.Elevation = 30
> > oChart.Rotation = 80
> > oChart.Pie3DGroup.VaryByCategories = True
> > oChart.HasTitle = True
> > oChart.ChartTitle.Top = 0
> > oChart.ChartTitle.left = 0
> > oChart.ChartTitle.Characters.Text = "Current Asset
> > Allocation"
> > oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
> > LegendKey:=False _
> > , HasLeaderLines:=True
> > oChart.SeriesCollection(1).DataLabels.Font.Size = 8
> >
> > oChart.HasLegend = True
> > With oChart.Legend
> > .Legend.Shadow = True
> > '.Legend.Position = xlLegendPositionRight
> > .Legend.AutoScaleFont = False
> > .Legend.Font.Size = 3
> > End With
> >
> >
> >
> > Thanks
> > Neil.
> >
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info

Andy Pope · 06-14-2006, 05:10 AM

Hi,

If you want you can send me, offline, your file and I will take a look.

Cheers
Andy

[email protected] wrote:
>>Does your code raise an error (assuming no error suppression is on) or
>>does it just not work?
>
>
> It simply does not work. No error messages are displayed.
>
> Again, when I tried it with your tweaked code, it made no difference.
> In fact if I set the font size to 30 instead of 3, it makes no
> difference. The legend remains exactly the same.
>
> Having looked through the documentation, most of the code seems
> correct, but Excel is not co-operating.
>
> The version of Word/Excel I am using is 9.02720 if this makes any
> difference.
>
> Thanks
> Neil.
>
> Andy Pope wrote:
>
>>Hi,
>>
>>Your chart title positioning code works for me in a test environment.
>>The legend part I had to tweak slightly.
>>
>> oChart.HasLegend = True
>> With oChart.Legend
>> .Shadow = True
>> .Position = xlLegendPositionRight
>> .AutoScaleFont = False
>> .Font.Size = 3
>> End With
>>
>>Does your code raise an error (assuming no error suppression is on) or
>>does it just not work?
>>
>>Cheers
>>Andy
>>
>>[email protected] wrote:
>>
>>>Hi,
>>>
>>>I'm currently struggling to change the Title and the Legend of pie
>>>charts that are generated by passing data from Word to Excel, then
>>>copying the chart over to Word. (I'm using Word + Excel 2000).
>>>
>>>The VBA is use works OK, and generates a reasonable looking chart.
>>>
>>>However, no matter what I try, I cannot alter the chart title, or the
>>>legend. Let me expand on this a little.
>>>
>>>1. The Chart Title is always centered. However, the legend (if there a
>>>lot of entries in the chart) spills over the title and obscures it.
>>>Therefore, I need to position the title on the far left of the chart.
>>>
>>>2. If I have more than, say, eight elements in the chart, the legend
>>>spills beyond the bottom of the chart, but there seems to be huge
>>>spacing gaps between each entry on the legend.
>>>However, if I create a chart using an Excel Range and the Chart Wizard,
>>>it seems to easily accomodate ten or more entries, and space them nice
>>>and evenly.
>>>
>>>It does not seem to matter whether I set "Legend.AutoScaleFont" to
>>>true or false, or whether I try and position the title. It always shows
>>>up the exactly the same.
>>>
>>>
>>>I thought I had cracked this, but I obviously need to do some fine
>>>tweaking. Any suggestions appreciated.
>>>
>>>
>>>
>>>
>>>Code below:
>>>
>>>//// cNumRows and cNumCols are variables used to populate an Excel
>>>Range from a Word VBA array of data
>>>
>>>Set oChart = oSheet.ChartObjects.Add.Chart
>>> oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
>>>cNumCols), PlotBy:= _
>>> xlColumns
>>> oChart.ChartType = xl3DPieExploded
>>> oChart.RightAngleAxes = True
>>> oChart.PlotArea.Height = 215
>>> oChart.PlotArea.Width = 215
>>> oChart.PlotArea.left = 5
>>> oChart.PlotArea.Fill.Visible = False
>>> oChart.PlotArea.Fill.Visible = False
>>> oChart.PlotArea.Border.LineStyle = -4142
>>> oChart.Elevation = 30
>>> oChart.Rotation = 80
>>> oChart.Pie3DGroup.VaryByCategories = True
>>> oChart.HasTitle = True
>>> oChart.ChartTitle.Top = 0
>>> oChart.ChartTitle.left = 0
>>> oChart.ChartTitle.Characters.Text = "Current Asset
>>>Allocation"
>>> oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
>>>LegendKey:=False _
>>> , HasLeaderLines:=True
>>> oChart.SeriesCollection(1).DataLabels.Font.Size = 8
>>>
>>> oChart.HasLegend = True
>>> With oChart.Legend
>>> .Legend.Shadow = True
>>> '.Legend.Position = xlLegendPositionRight
>>> .Legend.AutoScaleFont = False
>>> .Legend.Font.Size = 3
>>> End With
>>>
>>>
>>>
>>>Thanks
>>>Neil.
>>>
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info