suggestions to improve this code

[Raul]

I have the following commands nested in a VBA loop to
generate a number of charts with lines on 2 axes. It
works but I'm sure there's a way to choose the desired
chart type initially and clean up this code. Any
suggestions will be greatly appreciated.

Set NewChart = Charts.Add
' Had to start off with chart type xlLineMarkers
' and apply the desired chart type later in the code
' to eliminate crashes.

NewChart.ChartType = xlLineMarkers
NewChrtName = VarName
NewChart.SetSourceData Source:=DataRngArray(cntr), _
PlotBy:=xlColumns
NewChart.Location Where:=xlLocationAsNewSheet, _
Name:=NewChrtName

' Had to use ActiveChart because the code failed when
' NewChart was used. This is where the desired chart
' type is applied.

ActiveChart.PlotArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:= "Lines on 2 Axes"

NewChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
NewChart.Axes(xlCategory, xlSecondary).CategoryType =
xlCategoryScale

' end of code snippet


Thanks,
Raul

 

[Jon Peltier]

Raul -

You don't need to define any chart type right away. Often it works
better to set the source range, than define the type. Also the .Location
is redundant: the chart is already its own chart sheet, you just need to
name it.

So the code could go like this:

Set NewChart = Charts.Add

NewChart.SetSourceData Source:=DataRngArray(cntr), _
PlotBy:=xlColumns
NewChart.Name = VarName
NewChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:="Lines on 2 Axes"
NewChart.Axes(xlCategory, xlPrimary).CategoryType = _
xlCategoryScale
NewChart.Axes(xlCategory, xlSecondary).CategoryType = _
xlCategoryScale

In fact, you can shorten it slightly this way:

Set NewChart = Charts.Add

With NewChart
.SetSourceData Source:=DataRngArray(cntr), _
PlotBy:=xlColumns
.Name = VarName
.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:="Lines on 2 Axes"
.Axes(xlCategory, xlPrimary).CategoryType = _
xlCategoryScale
.Axes(xlCategory, xlSecondary).CategoryType = _
xlCategoryScale
End With

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

 

[Guest]

Jon,
I really appreciate the help.

Thanks,
Raul

-----Original Message-----
Raul -

You don't need to define any chart type right away. Often it works
better to set the source range, than define the type. Also the .Location
is redundant: the chart is already its own chart sheet, you just need to
name it.

So the code could go like this:

Set NewChart = Charts.Add

NewChart.SetSourceData Source:=DataRngArray(cntr), _
PlotBy:=xlColumns
NewChart.Name = VarName
NewChart.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:="Lines on 2 Axes"
NewChart.Axes(xlCategory, xlPrimary).CategoryType = _
xlCategoryScale
NewChart.Axes(xlCategory, xlSecondary).CategoryType = _
xlCategoryScale

In fact, you can shorten it slightly this way:

Set NewChart = Charts.Add

With NewChart
.SetSourceData Source:=DataRngArray(cntr), _
PlotBy:=xlColumns
.Name = VarName
.ApplyCustomType ChartType:=xlBuiltIn, _
TypeName:="Lines on 2 Axes"
.Axes(xlCategory, xlPrimary).CategoryType = _
xlCategoryScale
.Axes(xlCategory, xlSecondary).CategoryType = _
xlCategoryScale
End With

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



.