Extending a Chart Data Series from an Array - Can it be done?

  • Thread starter Thread starter Frank & Pam Hayes
  • Start date Start date
F

Frank & Pam Hayes

I am trying to create some VBA code that will extend a chart data series
from an Array. Google searches on the subject tell me that there is a known
bug that will not allow this. The code fails on the last
ActiveChart.SeriesCollection line. Any ideas for a workaround? Can I read
the array into a range somehow and then use Range with the Extend method?
Can I do this without writing the range somewhere? Here is the basic code
....


'Read all the datapoints into an array
TotalRows = Range("MyChartSeries").Count
Dim MyArray As Variant
ReDim MyArray(1 To TotalRows, 1 To 6)
MyArray = Worksheets("Data").Range("D9").Resize(TotalRows, 6)

'Extend the datapoints
For x = 1 To TotalRows
DataSeries = MyArray(x, 1)
xValue = MyArray(x, 4)
yValue = MyArray(x, 5)
ActiveChart.SeriesCollection(DataSeries).Extend xValue & "," & yValue,
Rowcol:=xlColumns, CategoryLabels:=True
Next x

Thanks,

Frank
 
Frank -

I've never tried the .Extend method with cells, never mind with arrays. I saw a flaw
in your code.

xValue & "," & yValue

does not make an array, it makes a string. I tried it with a real array, to no
avail. I suspect .Extend will not work this way.

The way to do this is to load the existing data into arrays, then add the new points
to these arrays, and feed them back into the series.

xVals =
yVals = activechart.seriescollection(1).values

redim xValues(1 to ubound(xVals) + 1)
redim yValues(1 to ubound(xVals) + 1)

for i=1 to ubound(xvals)
xValues(i) = xVals(i)
yValues(i) = yVals(i)
next

xValues(ubound(xvals) + 1) = newX
yValues(ubound(xvals) + 1) = newY

activechart.seriescollection(1).xvalues = xvalues
activechart.seriescollection(1).values = yvalues

One question: why complicate everything by taking perfectly good worksheet data and
using arrays? Arrays are trickier to manipulate, and you are limited to how much
data you can have in a series, because there's a character limit to the definition
of the X values and Y values. If you extend by another row, the formula stays the
same length (A1:A150 vs A1:A149), but if you add another value, the digits accrete
to the end of the array, until this limit is reached. The limit is something under
255 characters.

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

I am trying to handle multiple datacollections and thought the array would
be an easier way to do it.

I am currently plotting Revenue on the X axis and Variable Margin % on the Y
axis for a given product. I would like to add a Region dimension to the
chart with each region in a different series. The color of the symbol would
indicate the region in which the sale was made.

I am using an XLT template with a data sheet and a chart sheet. The intent
is that the user would copy their records to the data sheet and the chart
would automatically update. The chart is a simple XY Scatterchart with some
preformated text. The code works fine with a single data series, but I am
trying to customize it to handle multiple data series so that I can show the
additional region dimension.

The data sheet has a variable number of records with a variable number of
regions. For each record, I need to check the region and then extend the
appropriate seriescollection with the X and Y value. The records need to be
sorted on decending revenue in order to maintain some other non-chart
related calculations, so I can not sort on the Region Number.

I have already added code that checks the number of unique regions in the
data and adds a new series for each. Now the trick is to extend each of
those series as I cycle through the data.

Thanks,

Frank
 
Jon,

Here is the current VBA Code. The code assumes that you have a Workbook
with a worksheet called Data and a Chart Sheet called Chart1. The Data
worksheet is set up with Region Number (1,2, or 3) in Col A, the Revenue
number in Col C, and the Margin % in Col E. Columns B and D were
intentionally left blank because I wanted dis-contiguous data for my x and y
values. Row 1 is a header row and I put data in rows 2 through 11.

I set up the Chart by creating a scatterplot of the x any y values on the
data sheet and then deleting the series.

The VBA compiles and runs to completion, but it does not produce the result
I was looking for. It does not extend the series it adds. I think I have
something wrong in the syntax of the extend command, but I can not figure it
out. Any advice?


Option Explicit

Sub ExtendChartSeries()

'This code assumes a worksheet called "Data" with the
'Region Number is in Column A
'xValue (Revenue) is in Column C
'yValue (Var Margin) is in Column E
'D and E left blank to force non-contiguous logic

'There is a header row in Row 1
'There is actual data in Rows 2 through 11 for testing purpose only
'It also assumes a pre-existing chart of the Revenue and Margin

' The code to remove duplicates is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' This area defines the reange in which I want to look for unique items
Set AllCells = Worksheets("Data").Range("A2:A11")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

'Define the SeriesCollection
Dim SeriesCollection As Collection

'Add a ChartSeries for each unique item
Charts("Chart1").Activate
For Each Item In NoDupes
ActiveChart.SeriesCollection.NewSeries
Next Item

'this is test setting only ... will add code to determine actual
Dim TotalRows As Integer
TotalRows = 10

'Extend the datapoints
Dim DataRange As String
Dim DataSeries As Integer
Dim Row As Integer

Charts("Chart1").Activate
Row = 2
For Row = 2 To TotalRows + 1
'Determine the Region Number for the DataSeries
DataSeries = Sheets("Data").Range("A" & Row).Value
ActiveChart.SeriesCollection(DataSeries).Select

'Determine the X and Y Value
DataRange = "C" & Row & ",E" & Row

'Extend the series
ActiveChart.SeriesCollection.Extend
Source:=Sheets("Data").Range(DataRange), _
Rowcol:=xlColumns, CategoryLabels:=True
Next Row

End Sub
 
Frank -

I'm replying to both of your posts. First a few questions:

1.be an easier way to do it.<<

Arrays are not necessarily going to be easier than worksheet ranges, and worksheet
ranges will not be prone to some errors what will occur in arrays.

2. How do you determine region? It's not clear from your VBA procedure.

3. Again, you did not add an array using .Extend, but a string; also OLH says if you
use an array, the Rowcol and CategoryLabels arguments will be ignored, so it sounds
like 2D arrays are not welcome (I tried 1 and 2D). If you use a worksheet range for
the data, you could use .Extend with a range. The array technique I showed in my
earlier post does in fact work.

4.
Why?

Advice:

If your data comes from a well defined rectangular range, instead of .Extend, just
redefine the source data range:

ActiveChart.SetSourceData Source:=<range>, PlotBy:=xlColumns

It sounds like your data is in a nice list, so you could automate a pivot table to
arrange a plotting data range on an extra sheet. Make real charts from this data,
though, not pivot charts. Real charts are much more flexible.

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

I have imbedded my answers to your questions below. I do have one
additional question: Can you get the .Extend method to work with a
dis-contiguous range that has a single x and y value (versus a range with
multiple values)? I can not and I do not see what I am doing incorrectly.

Frank


Jon Peltier said:
Frank -

I'm replying to both of your posts. First a few questions:

1.
be an easier way to do it.<<

Arrays are not necessarily going to be easier than worksheet ranges, and
worksheet ranges will not be prone to some errors what will occur in
arrays.
Click to expand...
*** I agree. I have been using arrays in some of my code to speed
calculations and thought that since I already had the data in an array,
perhaps I could write it directly to a chart ***
2. How do you determine region? It's not clear from your VBA procedure.
Click to expand...
*** I determine the region using a defined name in the worksheet which
utilizes the Offset command to handle variable number of rows. Sorry I
forgot to account for that in the previous discussion. ***
3. Again, you did not add an array using .Extend, but a string; also OLH
says if you use an array, the Rowcol and CategoryLabels arguments will be
ignored, so it sounds like 2D arrays are not welcome (I tried 1 and 2D).
If you use a worksheet range for the data, you could use .Extend with a
range. The array technique I showed in my earlier post does in fact work.
Click to expand...
*** Jon, I took you advice in the previous post and stopped trying to use
the array. I am now trying to use .Extend with a range, but the range is
only a single X and Y value. Since the Sales region can change with every
record, I wanted to Extend one row at a time. I realize I could sort the
data and add a range as in your example, but I wanted to see if I could
avoid the intermediate step and add each row independently ... hence my
question above. ***
4.

Why?
Click to expand...
*** The application providing the source data to the users sends the data in
dis-contiguous columns.***
Advice:

If your data comes from a well defined rectangular range, instead of
.Extend, just redefine the source data range:

ActiveChart.SetSourceData Source:=<range>, PlotBy:=xlColumns

It sounds like your data is in a nice list, so you could automate a pivot
table to arrange a plotting data range on an extra sheet. Make real charts
from this data, though, not pivot charts. Real charts are much more
flexible.
Click to expand...
*** Jon, Thank you for the advice. I agree that I could copy the data
through either a pivot table or to a hidden sheet sorted on the region and
then use the above technique to produce the chart. ***
 
Frank -
I have imbedded my answers to your questions below. I do have one
additional question: Can you get the .Extend method to work with a
dis-contiguous range that has a single x and y value (versus a range with
multiple values)? I can not and I do not see what I am doing incorrectly.
Click to expand...

Tested and worked as expected, whether the added range is contiguous with the
existing source data or not:

Sub Macro()
ActiveChart.SeriesCollection.Extend _
Worksheets("Sheet1").Range("B11,D11"), xlColumns, True
End Sub

Categories for the rest of the chart were in column B and values in column D.

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

I appreciate your help with this. I have discovered the following:

1. When you have a chart with a single existing series that was created
using a Range of data, even it the range is a single xy value, and even if
the x and y values are noncontiguous, the code you wrote below will
successfully extend the dataseries.

2. When there are multiple dataseries in the chart, the extend command will
extend the range of ALL Range series in the collection. I thought that
selecting a specific SeriesCollection number (e.g. SeriesCollection(2))
prior to the extend command would limit the extension to the single series
selected, but I could not get this to work.

3. If the dataseries was created by putting specific values in the chart
(typing the specific x and y values into the Chart - Source Data - Add
dialog box rather than referring to the range on the associated sheet) this
dataseries is treated as an Array and the extend command will not work with
it.

4. If you have added a new series via VBA but have not populated it yet,
Excel will not let you extend the series. I assume it is in there as an
Array and therefore #3 above applies. This was the problem with my original
code posted in the thread.

Jon ... Any advice on #2 above?

Frank
 
Frank -

I try to avoid working with all my series together. Unless it's a simple chart, I
don't use SetSourceData, and I've never used Extend in battle, only for this thread.
I adjust each series as a separate entity, and control the name, values, and xvalues
of each independently of the others. It seems like more work, but it really isn't,
and you gain more control over your chart.

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