chart with macro

[premuratus]

i record a macro which plot a chart getting its data from sheet1's columns.
it's works inwhich created Excel workbook but when i try to run in different
Excel workbook which has different name, macro returns "unable to set Xvalues
property of series class" eror. I think this is because of the column
adressing for "chart series data". How can i create a macro which gets its
data from active workbook's active sheet columns?

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData
Source:=ThisWorkbook.Sheets("Sheet1").Range("H100")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'code fail here...
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!C5"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!C4"

 

[Mat P:son]

Hi there,

I think that your VBA macro contains a few redundant pieces of code: if you
set the source data you don't need to worry about the SeriesCollection, and
vice versa. You don't have to try to set both.

If you:

1) add the following method to your workbook,
2) put some values in to a sheet (e.g., X values from A1 downwards, and
Y-values from B1 downwards),
3) Select all the values in the sheet
4) Run the macro

....Then you'll get a new chart in your workbook. There will be a new plot in
the chart, and you will be able to refer to it as SeriesCollection(1).


Sub CreateAnXyPlotPlease()
' Get hold of the current selection (it will change)
Dim s As Range
Set s = Selection

' Add a new chart to the workbook
Dim c As Chart
Set c = Charts.Add

' Change the chart into an XY plot, and define
' from where its data should be retrieved.
c.ChartType = xlXYScatterSmooth
c.SetSourceData _
Source:=s, _
PlotBy:=XlRowCol.xlColumns
End Sub

 

[premuratus]

but what about if i need 2 series collection what i mean if i have 4 column
for chart data (2 series in the chart) ?
how i select the source

 

[Mat P:son]

Your first column (col A, or whatever) will be your X-values, while the
following columns will define one new graph (series) for each column. Just
select larger ranges (i.e., more data columns)

If you want to specify different sets of X-values for each series, then skip
the SetSourceData call altogether, and add series explicitly instead.

So, really, it's either or...: SetSourceData if you've got a bunch of graphs
with a common set of X-values, or explicit calls to NewSeries if you need
more flexibility.

Look at this (and note that I explicitly define from which sheet I fetch my
data, in order to avoid confusion: as soon as you add a new chart you will
change the active sheet from Sheet1 to the new chart, so the calls to Range()
would fail):

Sub CreateAnXyPlotAgainPlease()
' Add a new chart to the workbook
Dim c As Chart
Set c = Charts.Add

' Change the chart into an XY plot, and define
' from where its data should be retrieved.
c.ChartType = xlXYScatterSmooth

' Define a new series, s1, and get the data from sheet1.
Dim s1 As Series
Set s1 = c.SeriesCollection.NewSeries
s1.XValues = Worksheets("Sheet1").Range("A1:A6")
s1.Values = Worksheets("Sheet1").Range("B1:B6")
s1.Name = "1st Graph"

' Another series, s2, with a separate set of X values
Dim s2 As Series
Set s2 = c.SeriesCollection.NewSeries
s2.XValues = Worksheets("Sheet1").Range("C1:C5")
s2.Values = Worksheets("Sheet1").Range("D15")
s2.Name = "2nd Graph"
End Sub

 

[premuratus]

thank you very very verrrryy muchhhh
it is very simple good and flexible way
again thanks..

 

[Mat P:son]

No probs -- it can be pretty tricky sometimes to get one's head around these
things.

Oh, and a tip for the future: try not to rely too heavily on "implicit
objects", i.e., when you refer to for example a Range, try to write something
like:

Worksheets("Sheet1").Range("A1:E5")

Or maybe even:

Sheet1.Range("A1:E5")

Because if you only write:

Range("A1:E5")

....then you're implicitly refering to your active sheet, but the active
sheet may currently be a chart, in which case this call will fail miserably
(this was what happened in your code) -- and in some situations you may not
even have an active sheet! Dropping the object qualifier is a common way of
doing things in Excel VBA code, and it causes much confusion and plenty of
bugs.

So, my advice is: when programming, always say what you mean, and mean what
you say -- be explicit about which objects you're working with. Avoid
implicit references to objects, because by dropping the object qualifier
you're suddenly making assumptions, and assumptions are error-prone.

Good luck with your Excel hacking )
/MP

 

[premuratus]

thank you again...
i got the message..
thanks thanks thanks thanks thanks thanks...