Record Macro 1

[bertchan2003]

Hi,

I've recorded a macro for creating a 3D line chart. But whenever I run the macro, it stops at:

ActiveChart.SeriesCollection(1).XValues = "=RiskbyFunc!R4C3:R18C3"

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

Any clue?

Here is the code recorded in the macro:

Sub Create3DChart()
Charts.Add
ActiveChart.ChartType = xl3DLine
ActiveChart.SetSourceData Source:=Sheets("RiskbyFunc&quot.Range("C2:I18&quot, _
PlotBy:=xlColumns

While ActiveChart.SeriesCollection.Count > 0
ActiveChart.SeriesCollection(1).Delete
Wend

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = "=RiskbyFunc!R4C3:R18C3"
ActiveChart.SeriesCollection(1).Values = "=RiskbyFunc!R4C6:R18C6"
ActiveChart.SeriesCollection(1).Name = "=""Function 1"""

ActiveChart.SeriesCollection(2).XValues = "=RiskbyFunc!R4C3:R18C3"
ActiveChart.SeriesCollection(2).Values = "=RiskbyFunc!R22C6:R36C6"
ActiveChart.SeriesCollection(2).Name = "=""Function 2"""

ActiveChart.SeriesCollection(3).XValues = "=RiskbyFunc!R4C3:R18C3"
ActiveChart.SeriesCollection(3).Values = "=RiskbyFunc!R40C6:R54C6"
ActiveChart.SeriesCollection(3).Name = "=""Function 3"""

ActiveChart.Location Where:=xlLocationAsObject, Name:="RiskbyFunc"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Release Risk over time at " + "Functional level"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Day"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Risk"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Functions"
End With
End Sub

Thanks

 

[RobBroekhuis]

You need to use range expressions to assign to the XValues and Values properties. Substitute your assignments with something like the following:

ActiveChart.SeriesCollection(1).XValues = _
sheets("RiskbyFunc&quot.range("C4:C18&quot
Rob

 

[bertchan2003]

Hi RobBroekhuis,

I replaced my code with what you suggested and it works on the first series, but not with the last two.

Here is my new code for the subroutine:

Sub Create3DChart()

Charts.Add
ActiveChart.ChartType = xl3DLine
ActiveChart.SetSourceData Source:=Sheets("RiskbyFunc&quot.Range("C2:I18&quot, _
PlotBy:=xlColumns

While ActiveChart.SeriesCollection.Count > 1
ActiveChart.SeriesCollection(1).Delete
Wend

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Sheets("RiskbyFunc&quot.Range("C4:C18&quot
ActiveChart.SeriesCollection(1).Values = Sheets("RiskbyFunc&quot.Range("F4:F18&quot
ActiveChart.SeriesCollection(1).Name = "=""Function 1"""


ActiveChart.SeriesCollection(2).XValues = Sheets("RiskbyFunc&quot.Range("C4:C18&quot
ActiveChart.SeriesCollection(2).Values = Sheets("RiskbyFunc&quot.Range("F22:F36&quot
ActiveChart.SeriesCollection(2).Name = "=""Function 2"""

ActiveChart.SeriesCollection(3).XValues = Sheets("RiskbyFunc&quot.Range("C4:C18&quot
ActiveChart.SeriesCollection(3).Values = Sheets("RiskbyFunc&quot.Range("F40:F54&quot
ActiveChart.SeriesCollection(3).Name = "=""Function 3"""


ActiveChart.Location Where:=xlLocationAsObject, Name:="RiskbyFunc"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Release Risk over time at " + "Functional level"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Day"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Risk"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Functions"
End With
End Sub

 

[RobBroekhuis]

How does it not work? Do you get an error message? Your code has several oddities (creating a chart with several series, only to remove all but one of them, for example). I suggest the following might work nicely:

Sub Create3DChart()
Charts.Add
with activechart
.ChartType = xl3DLine
.SetSourceData Source:=Sheets("RiskbyFunc&quot.Range("C4:F18&quot, PlotBy:=xlColumns
with .SeriesCollection(1)
.Values = Sheets("RiskbyFunc&quot.Range("F4:F18&quot
.Name = "Function 1"
end with
with .SeriesCollection(2)
.Values = Sheets("RiskbyFunc&quot.Range("F22:F36&quot
.Name = "Function 2"
end with
with .SeriesCollection(3)
.Values = Sheets("RiskbyFunc&quot.Range("F40:F54&quot
.Name = "Function 3"
end with
.Location Where:=xlLocationAsObject, Name:="RiskbyFunc"
.HasTitle = True
.ChartTitle.Characters.Text = "Release Risk over time at " + "Functional level"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Day"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Risk"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Functions"
End With
End Sub

Give it a try, and if it doesn't work, let us know how it's failing.
Rob

 

[bertchan2003]

Thanks RobBroekhuis. That bascially works.

However, the data series in my excel worksheet are not contiguous, ie the x-series is on C4:C18, the data series (Y values) are in F4:F18, F22:F36, F40:F54....and so on (with a few more data series possibly). So my question is with what to specify on the range of

SetSourceData Source:=Sheets("RiskbyFunc&quot.Range("C4:F18&quot, PlotBy:=xlColumns

From microsoft MSDN, range contains the source data, but if discontiguous data series, is there any way to workaround that?

Thanks

 

[RobBroekhuis]

That's why I first just specified the datasource as a contiguous block, then went back and respecified the .values properties of individual series. You may be able to do the same with non-contiguous ranges (using the union() function in VBA), but the results may be unpredictable (e.g., what is used as x range may be different from what you expect). I prefer to do it in two steps as per my post above. What is not working in the code I proposed?

Rob

 

[bertchan2003]

Hi RobBroekhuis,

It works basically. Because I'll have unknown number of series to be added to the chart (though all the series are in the same column), I'm looking for a smarter way of generating the 3D plot, but have no idea of what the .range has to do with that when I can specify the .values properties of individual series.

Hubert

 

[bertchan2003]

Hi RobBroekhuis,

To flesh out the problem a bit, on the 3D line chart, I want the x-data from C4:C18, while the Y-data are from F4:F18, F22:F36, F40:F54....

I tried ie, Range("F4:F18,F22:F36&quot.select, but what it gives is a single line composed of these 2 series.

Thanks in advance.

 

[RobBroekhuis]

Okay, here's a suggestion:
Say the number of data series is contained in a variable called iSeries. Now you can use:

.SetSourceData Source:=Sheets("RiskbyFunc&quot.Range("C4:C18",range("C4:C18&quot.offset(0,iSeries)), PlotBy:=xlColumns
for i=1 to iSeries
with .SeriesCollection(i)
.Values = Sheets("RiskbyFunc&quot.Range("F4:F18&quot.offset(18*i-18,0)
.Name = "Function " & i
end with
next i
Rob

 

[bertchan2003]

Hi RobBroekhuis,


That works!!! Thanks.l

 

[xlbo]

Bertchan2003 - the way to say thanks on TekTips is to award a star - this is not only the recognised way of saying thanks but it also hapls point people who use the search functionality to only look at those posts where a resolution has been given Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

Shouls also point out that you do this by clicking on the link at the bottom of the appropriate post : "Mark This Post as a helpful / Expert Post" Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde