Creating a new chart- why Set NewChart = Charts.Add doesn't work?

[Thief_]

I've got the following code:

Charts.Add
Set NewChart = Charts.Add
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
With NewChart
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

The code fails at the line ".HasTitle = True". Viewing the NewChart object
in the Watch window show the object has NO properties, but I don't
understand why since it is dimmed as a chart object. The only way to make
this code work is to change it to:

Charts.Add
Set NewChart = ActiveChart
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
With NewChart
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

Can someone explain why this is so?

 

[Thief_]

Sorry, it seems that the line:

NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"

which moves the chart to a worksheet as an object, kills all the chart's
properties- this is so confusing!

 

[Andy Pope]

Hi,

I think it's that the NewChart object is a Chart when it's a chart sheet
but moving to the worksheet means its now a ChartObject, which has a
Chart reference. The variable does not automatically adjust itself.

Either format the chart and then move it or re reference NewChart after
you move it.

Set NewChart = Charts.Add
NewChart.ChartType = xlLineMarkers
NewChart.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
NewChart.Location Where:=xlLocationAsObject, Name:="GraphResults"
Set NewChart = ActiveChart
With NewChart
.ChartType = xlLineMarkers
.SetSourceData Source:=rng2Plot, PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Characters.Text = Title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Num Errors"
End With

Cheers
Andy

 

[Jon Peltier]

Here's a simpler way. Make the chart object directly in the worksheet, without the
chart sheet as an intermediate step:

Set NewChart = ActiveSheet.ChartObjects.Add(100, 100, 300, 250).Chart

where the numbers in parentheses are the left, top, width, and height of the chart
object, in points.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tushar Mehta]

No, it doesn't 'kill' the chart's properties. What happens is that
when the chart is moved to a worksheet, the object to which NewChart
points no longer exists. The correct way to relocate the chart and
continue to have a reference to it is to reestablish the reference to
the new object. Use

Set NewChart=NewChart.Location ( _
Where:=xlLocationAsObject, Name:="GraphResults")

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Or, as I try to encourage people to do, create it as a chart object to
begin with.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tushar Mehta]

Yeah, I know you like to start with a chartobject. I don't. Except in
some specific circumstances, I prefer to let XL decide on the dimensions
of the chartobject based on the current window size.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Jon Peltier]

Hi Tushar -

That's fine if it's going to be an on-screen thing, but if it's intended for a
printed report, it's better to size it yourself. Besides, my retentive nature likes
charts that line up with the cell boundaries.

You can approximate the default Excel size if you make the chart half as wide and
tall as the active pane's usable size (subject to certain minimum values, of
course), centered within the range appearing in that pane.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______