SetSourceData for UserType XYChart with two series

[PBezucha]

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

 

[Jon Peltier]

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/ChartsHowTo/QuickChartVBA.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

 

[PBezucha]

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