Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 120
Default SetSourceData for UserType XYChart with two series

Jon and the others,
Imagine I am to depict routinely series of recorded points by means of a
user defined chart, where one chart series are the mentioned points, and
another one is a user defined regression function. As I do not intend to
stuff numerous sheets in a workbook with excessive charts, I would prefer
macro that is able to present, promptly and temporarily, a neatly formatted
user chart, fed with actual values. When using usual commands

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= . . .

the conflict, however, comes in:

ActiveChart.SetSourceData Source:= . . .

Assigning a simple (double-)block for the first of the series is trivial
here. But the second series, though primarily contained in the template
chart, virtually disappears and thus cannot be declared by a following
..SeriesCollection(2). It requires adding the second Series, with necessary
loss of original format.
An idea arises that - if the Source:= could be declared as to comprise both
of data series by some sort of Range combinations or so - the problem would
be solved. Has somebody sought such a solution?
As a remark, my way out now is copying the template chart from a workbook
with getting data from the actual worksheet. But still I succumbed to a
temptation to seek a smart idea.
Sorry for my submitting marginal topics.

Petr
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default SetSourceData for UserType XYChart with two series

Sometimes Excel is picky about the order of statements in your VBA
procedures. Pickier than the macro recorder, so that sometimes recorded code
doesn't do what you expect. I have found that Excel would rather define the
data before the chart type. Try rearranging your VBA steps in that manner.

If the problem is that there is no series 2 in the new chart, you can add
the series using ActiveChart.SeriesCollection.NewSeries, as I describe in
this page:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

Do this before applying the custom chart type (as I describe above).

I don't work much with custom chart types. Through years of hacking away, my
user chart gallery has been compromised, and I cannot test this reliably.

Is your user defined regression simply a set of formulas in the sheet, and
these are plotted as series 2? I would go so far as to suggest creating an
Excel template as follows:

Make a workbook with a single worksheet. Put some typical data in the sheet,
and do your calculations as well. Create a chart with these sets of data,
and format it to your liking. Save this as a template. Whenever you need
this chart, insert a sheet based on this template (use right-click on the
sheet tab to see the templates). Paste your new data over the old data on
the template, and the calculations and chart will update automagically. This
process can readily be automated as well.

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


"PBezucha" wrote in message
...
Jon and the others,
Imagine I am to depict routinely series of recorded points by means of a
user defined chart, where one chart series are the mentioned points, and
another one is a user defined regression function. As I do not intend to
stuff numerous sheets in a workbook with excessive charts, I would prefer
macro that is able to present, promptly and temporarily, a neatly
formatted
user chart, fed with actual values. When using usual commands

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= . . .

the conflict, however, comes in:

ActiveChart.SetSourceData Source:= . . .

Assigning a simple (double-)block for the first of the series is trivial
here. But the second series, though primarily contained in the template
chart, virtually disappears and thus cannot be declared by a following
.SeriesCollection(2). It requires adding the second Series, with necessary
loss of original format.
An idea arises that - if the Source:= could be declared as to comprise
both
of data series by some sort of Range combinations or so - the problem
would
be solved. Has somebody sought such a solution?
As a remark, my way out now is copying the template chart from a workbook
with getting data from the actual worksheet. But still I succumbed to a
temptation to seek a smart idea.
Sorry for my submitting marginal topics.

Petr



  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 120
Default SetSourceData for UserType XYChart with two series

Jon,
Late but still have my thanks. One appeases always his conscience being
ascertained he does not pass any should-be-known straightforward path.
Petr

--
Petr Bezucha


"Jon Peltier" wrote:

Sometimes Excel is picky about the order of statements in your VBA
procedures. Pickier than the macro recorder, so that sometimes recorded code
doesn't do what you expect. I have found that Excel would rather define the
data before the chart type. Try rearranging your VBA steps in that manner.

If the problem is that there is no series 2 in the new chart, you can add
the series using ActiveChart.SeriesCollection.NewSeries, as I describe in
this page:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

Do this before applying the custom chart type (as I describe above).

I don't work much with custom chart types. Through years of hacking away, my
user chart gallery has been compromised, and I cannot test this reliably.

Is your user defined regression simply a set of formulas in the sheet, and
these are plotted as series 2? I would go so far as to suggest creating an
Excel template as follows:

Make a workbook with a single worksheet. Put some typical data in the sheet,
and do your calculations as well. Create a chart with these sets of data,
and format it to your liking. Save this as a template. Whenever you need
this chart, insert a sheet based on this template (use right-click on the
sheet tab to see the templates). Paste your new data over the old data on
the template, and the calculations and chart will update automagically. This
process can readily be automated as well.

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


"PBezucha" wrote in message
...
Jon and the others,
Imagine I am to depict routinely series of recorded points by means of a
user defined chart, where one chart series are the mentioned points, and
another one is a user defined regression function. As I do not intend to
stuff numerous sheets in a workbook with excessive charts, I would prefer
macro that is able to present, promptly and temporarily, a neatly
formatted
user chart, fed with actual values. When using usual commands

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= . . .

the conflict, however, comes in:

ActiveChart.SetSourceData Source:= . . .

Assigning a simple (double-)block for the first of the series is trivial
here. But the second series, though primarily contained in the template
chart, virtually disappears and thus cannot be declared by a following
.SeriesCollection(2). It requires adding the second Series, with necessary
loss of original format.
An idea arises that - if the Source:= could be declared as to comprise
both
of data series by some sort of Range combinations or so - the problem
would
be solved. Has somebody sought such a solution?
As a remark, my way out now is copying the template chart from a workbook
with getting data from the actual worksheet. But still I succumbed to a
temptation to seek a smart idea.
Sorry for my submitting marginal topics.

Petr




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fill Series Dates: not letting me change the series from year to m Mike Excel Discussion (Misc queries) 1 January 24th 08 06:08 PM
defined names and setsourcedata Jason Morin Charts and Charting in Excel 2 December 5th 07 04:39 PM
charting problem with activechart.setsourcedata Mary Kathryn Excel Discussion (Misc queries) 4 February 25th 06 09:54 PM
chart data series -- plot a table as a single series hjc Charts and Charting in Excel 7 September 20th 05 05:52 PM
series graph -- one series being added to another series rich zielinski via OfficeKB.com Charts and Charting in Excel 3 March 30th 05 06:23 PM


All times are GMT +1. The time now is 10:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"