Using VBA to create and format Chart

[Greg Fish]

After getting through, what I thought was the hard part, that is solving the
riddle of

Run-time error '1004': Unable to set the XValues property of the Series class

My next question is how do you go about programmatically formatting a chart.
I have recorded a macro, and taken code snippets from the macro, and
attempted to implement the code from the macro but inevitable it leads to
errrors. I have seen several examples of the following code

With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With

however, when I attempt to use the above it does not work


Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As
String, curve As String)
Dim objWorkSheet As Object
Dim Y_data_range, X_data_range As Range
Dim Y_est_data_range, X_est_data_range As Range
Dim sY_data_range As String
Dim sX_data_range As String
Dim sY_est_data_range As String
Dim sX_est_data_range As String
Dim fittedCurve As String
Dim fuelCurve As ChartObject
Dim seriesData As Series
Dim seriesEst As Series
Worksheets(2).Activate
Set Y_data_range = ActiveSheet.Range(y1Axis)
Set X_data_range = ActiveSheet.Range(x1Axis)
Set Y_est_data_range = ActiveSheet.Range(y_EST)
Set X_est_data_range = ActiveSheet.Range(x_EST)
sY_data_range = "'" & ActiveSheet.Name & "'!" &
Y_data_range.Address(ReferenceStyle:=xlR1C1)
sX_data_range = "'" & ActiveSheet.Name & "'!" &
X_data_range.Address(ReferenceStyle:=xlR1C1)
sY_est_data_range = "'" & ActiveSheet.Name & "'!" &
Y_est_data_range.Address(ReferenceStyle:=xlR1C1)
sX_est_data_range = "'" & ActiveSheet.Name & "'!" &
X_est_data_range.Address(ReferenceStyle:=xlR1C1)
fittedCurve = curve

Worksheets(1).Activate
Set fuelCurve = ActiveSheet.ChartObjects.Add _
(Left:=150, Width:=750, Top:=25, Height:=450)
With fuelCurve.Chart

'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth
'.ChartTitle ' add string
Selection.Interior.ColorIndex = xlNone
'
' add series from selected name ranges

With .SeriesCollection.NewSeries
.Values = "=" & sY_data_range
.XValues = "=" & sX_data_range
.Name = "Data"
' .LineStyle = xlNone
' .Trendlines.Add(Type:=xlExponential)
End With

With .SeriesCollection.NewSeries
.Values = "=" & sY_est_data_range
.XValues = "=" & sX_est_data_range
.Name = "Estimated"
End With
End With
With Excel.ActiveWorkbook.ActiveChart.Axes(xlCategory) ERROR
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With Excel.ActiveWorkbook.ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
End Sub


Results in a

Runtime Error '91'
Object varialble or With block varaible not set

 

[Nigel]

I know this works for me

ActiveWorkbook.Sheets("Sheet5").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Graphdata").Range("A4:B346"),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Cur Yr"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Graphdata!R5C3:R346C3"
ActiveChart.SeriesCollection(2).Name = "=""Prev Yr"""
ActiveChart.SeriesCollection(3).Values = "=Graphdata!R5C9:R346C9"
ActiveChart.SeriesCollection(3).Name = "=""Budget"""
ActiveChart.SeriesCollection(1).XValues = "=Graphdata!R5C1:R346C1"
ActiveChart.SeriesCollection(1).Values = "=Graphdata!R5C2:R346C2"
ActiveChart.SeriesCollection(2).XValues = "=Graphdata!R5C1:R346C1"
ActiveChart.SeriesCollection(3).XValues = "=Graphdata!R5C1:R346C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Billings"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 3000000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
End With
ActiveChart.PlotArea.Select
With Selection.Interior
.ColorIndex = 40
.PatternColorIndex = 1
.Pattern = xlSolid
End With
Selection.Left = 6
Selection.Width = 6596
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.Size = 8
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.Size = 8
End With


ActiveSheet.Shapes("Chart 1").IncrementLeft -297#
ActiveSheet.Shapes("Chart 1").IncrementTop -158.25
ActiveSheet.Shapes("Chart 1").ScaleWidth 10.78, msoFalse,
msoScaleFromTopLeft

ActiveChart.Legend.Select
Selection.Left = 0
Selection.Top = 1
Selection.Width = 250
Selection.Height = 18
ActiveChart.ChartTitle.Select
Selection.Left = 0
Selection.Top = 24
Range("A28").Select

it will also place it on the left side of the sheet

 

[Greg Fish]

Nigel -

thank you for the reply it will take me some time to digest your code
example.

With respect to trendlines, and for XY scatter plots, is it a matter of
using a series based method, or some other method to modify this attributes.
When used the macro recorder the following code was generated

ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlExponential, Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select

I would have like to some how implement the following

With .SeriesCollection.NewSeries
.Values = "=" & sY_data_range
.XValues = "=" & sX_data_range
.Name = "Data"
.LineStyle = xlNone
.Trendlines.Add(Type:=xlExponential)
End With

this however does not work, and errors are generated by .LineStyle and
..Trendlines.Add method calls.

 

[Jon Peltier]

Item 1: Did you solve the "Unable to set the XValues property of the Series
class" problem? Your intro isn't clear. If not, check what data the series
points to before you try to reassign it. Probably not related, but in the
following line, you are declaring Y_data_range as a Variant and only
X_data_range as a Range:

Dim Y_data_range, X_data_range As Range

Item 2:

With Excel.ActiveWorkbook.ActiveChart.Axes(xlCategory) ERROR

Earlier you assigned the chart object to a variable, FuelCurve. Rather than
depending on what might be the active chart (which isn't an object of a
workbook anyway), use the variable you declared and assigned:

With FuelCurve.Chart.Axes(xlCategory)

Item 3: Smoothed lines on a line or XY chart distort the data. Don't believe
me? Plot these points as a smooth XY chart and tell me what the maximum
value is:

1 1
2 6
3 6


- Jon

 

[Greg Fish]

Jon

with regard to item 1, that has solved and it was a rather vexing issue.
Mainly I am interested know, in the proper way of invoking the different
methods to format a chart.

For example I created this macro

Sub formatChart()
'
' formatChart Macro
' Macro recorded 1/29/2007 by GFish
'

'
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 1
.LineStyle = -1
End With
Selection.Interior.ColorIndex = xlNone
Sheets("Dashboard").DrawingObjects("Chart 90").RoundedCorners = False
Sheets("Dashboard").DrawingObjects("Chart 90").Shadow = False
ActiveChart.Axes(xlValue).MinorGridlines.Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlExponential,
Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select
End Sub


What I would like to do is invoke code such as
SeriesCollection().Trendlines.Add, but within my code when the series is
created, that just seems logical to me, if however it does not work, or
requires an arcane workaround such as

Set Y_est_data_range = ActiveSheet.Range(y_EST)
sY_est_data_range = "'" & ActiveSheet.Name & "'!"
Y_est_data_range.Address(ReferenceStyle:=xlR1C1)

which is how the first item was solved, and, something that I never would
have figured out without the help of Peter T.

 

[Greg Fish]

Jon

using With FuelCurve.Chart.Axes(xlCategory)

worked like a champ.

I just need to figure out the proper method of removing the lines that
connect the points for the series 'Data', for I just want these to show up as
scatter points. I have tried using;

With fuelCurve.Chart.SeriesCollection(1)
' .LineStyle = xlNone
If fittedCurve = "Exponent" Then
.Trendlines.Add Type:=xlExponential
Else
.Trendlines.Add Type:=xlLogarithmic
End If

which works fine for adding the trendlines, but not so for changing the
LineStyle, I believe that .LineStyle may not be part of the SeriesCollection
object, which is the problem. I have gained a tremendous amount of knowledge
working on this project and from the posts on this forum. Thanks for your
help.

Greg

 

[Jon Peltier]

Left out a piece: .Border

With fuelCurve.Chart.SeriesCollection(1)
.Border.LineStyle = xlNone

- Jon

 

[Greg Fish]

Jon -

I was able to figure that out, prior to reading your post. Of course your
website was illuminating and pointed me in the right direction. I ran the
macro recorded for individual actions that I wanted to do, so as change the
line type. Anyhow here is the complete code for others to look at, should it
prove helpful. Likewise a critique would be useful, I am still in the
learning process, and consider myself to be a novice with regard to overall
VBA profeciency.

Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As
String, curve As String, xLabel As String, yLabel As String)
Dim objWorkSheet As Object
Dim Y_data_range, X_data_range As Range
Dim Y_est_data_range, X_est_data_range As Range
Dim sY_data_range As String
Dim sX_data_range As String
Dim sY_est_data_range As String
Dim sX_est_data_range As String
Dim fittedCurve As String
Dim xAxisLabel As String
Dim yAxisLabel As String
Dim fuelCurve As ChartObject
Dim seriesData As Series
Dim seriesEst As Series
Worksheets(2).Activate
Set Y_data_range = ActiveSheet.Range(y1Axis)
Set X_data_range = ActiveSheet.Range(x1Axis)
Set Y_est_data_range = ActiveSheet.Range(y_EST)
Set X_est_data_range = ActiveSheet.Range(x_EST)
sY_data_range = "'" & ActiveSheet.Name & "'!" &
Y_data_range.Address(ReferenceStyle:=xlR1C1)
sX_data_range = "'" & ActiveSheet.Name & "'!" &
X_data_range.Address(ReferenceStyle:=xlR1C1)
sY_est_data_range = "'" & ActiveSheet.Name & "'!" &
Y_est_data_range.Address(ReferenceStyle:=xlR1C1)
sX_est_data_range = "'" & ActiveSheet.Name & "'!" &
X_est_data_range.Address(ReferenceStyle:=xlR1C1)
fittedCurve = curve
xAxisLabel = xLabel
yAxisLabel = yLabel

Worksheets(1).Activate
Set fuelCurve = ActiveSheet.ChartObjects.Add _
(Left:=150, Width:=750, Top:=25, Height:=450)
With fuelCurve.Chart

'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth
'.ChartTitle ' add string
'
' add series from selected name ranges

With .SeriesCollection.NewSeries
.Values = "=" & sY_data_range
.XValues = "=" & sX_data_range
.Name = "Data"
End With

With .SeriesCollection.NewSeries
.Values = "=" & sY_est_data_range
.XValues = "=" & sX_est_data_range
.Name = "Estimated"
End With
End With
With fuelCurve.Chart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Characters.Text = xAxisLabel
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With fuelCurve.Chart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = yAxisLabel
.HasMajorGridlines = True
.HasMinorGridlines = True
End With
With fuelCurve.Chart.SeriesCollection(1)
With .Border
.Weight = xlThin
.LineStyle = xlNone
End With
If fittedCurve = "Exponent" Then
.Trendlines.Add Type:=xlExponential
Else
.Trendlines.Add Type:=xlPolynomial
End If
End With
fuelCurve.Chart.SeriesCollection(1).Trendlines(1).DisplayEquation = True
fuelCurve.Chart.PlotArea.Interior.ColorIndex = xlNone
End Sub