Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
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" wrote: 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
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("D1:D5") s2.Name = "2nd Graph" End Sub "premuratus" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
thank you very very verrrryy muchhhh
it is very simple good and flexible way again thanks.. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
No probs -- it can be pretty tricky sometimes to get one's head around these
things. Oh, and a tip for the futu 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 :o) /MP "premuratus" wrote: thank you very very verrrryy muchhhh it is very simple good and flexible way again thanks.. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart with macro
thank you again...
i got the message.. thanks thanks thanks thanks thanks thanks... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change position of chart labels on line chart | Charts and Charting in Excel | |||
Chart macro | Charts and Charting in Excel | |||
About chart with a macro | Excel Programming | |||
Chart using Macro | Charts and Charting in Excel | |||
Chart Macro | Excel Programming |