XValues series breaks on disjoint ranges - but works fine in Excel 2007

[laneer]

I'm creating an app that creates a scatter plot based on two columns
of data in the spreadsheet. It breaks and throws a "Run-time error
1004 - unable to set the XValues property of the series class" under
very specific circumstances. The app allows one to click data point
labels on a chart to include/exclude them from the plot. The data
points are toggled into and out of the plot this way. Here is the
relevant code:

========================
Function RebuildSeries_2() As Integer
Dim tempInt As Integer, tempRange As Range
Set myRange = BuildMyRange(DataAreaLeft, DataAreaRight,
DataAreaTop, DataAreaBottom, nDataPoints, DataPointArray)
ActiveChart.SeriesCollection(2).XValues = myLeftsideRange '
breaks on this line
ActiveChart.SeriesCollection(2).Values = myRightsideRange ' also
breaks on this line
End Function
========================

It breaks on the
ActiveChart.SeriesCollection(2).XValues = myLeftsideRange '
breaks on this line
line.

The "BuildMyRange" function uses a lookup table to build ranges for
the scatterplot. E.g., it will add in all the desired cells in one
column into the range "myLeftsideRange". Does the same for the right
side range. The code for that function is at the end of this message.

This works perfectly well in Excel 2007, but fails in Excel 2000 if
the range created is disjoint (e.g., if there is a gap in the column
of cells used to create the range). The range builder always builds up
the range from individual cells, so I don't think the problem is
having a range built up that way.

It works correctly in Excel 2000 so long as the created range is
contiguous. I need to get it working for both Excel 2000 and Excel
2007.

Thanks for any help.

Larry Neer


========================
Function BuildMyRange(DataAreaLeft As Integer, DataAreaRight As
Integer, DataAreaTop As Integer, _
DataAreaBottom As Integer, nDataPoints As Integer,
DataPointArray() As Boolean) As Range
Dim i As Integer, tempRange As Range, SFlag As Boolean

' Excel barfs if I try to build a range starting with a null
(nothing) range.
' use a Flag here to get around it (flag is false until find
the first included range)
SFlag = False

For i = 1 To nDataPoints
If (DataPointArray(i)) Then
If (Not SFlag) Then
SFlag = True
Set BuildMyRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set myLeftsideRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaLeft))
Set myRightsideRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaRight), ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaRight))
End If
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set BuildMyRange = Union(BuildMyRange, tempRange)
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaLeft))
Set myLeftsideRange = Union(myLeftsideRange, tempRange)
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaRight), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set myRightsideRange = Union(myRightsideRange, tempRange)
End If
Next i

End Function
========================

 

[Jon Peltier]

Assuming you have at least two series (you'd get a subscript out of range
error if that weren't the case) and myLeftsideRange is a valid range, what
could be causing the error is in not having a valid range for the series X
and Y values before running this code.

Marker series (XY and Line) choke on this, but if you temporarily change to
a fill series (column or area), you can access the series data even if the
data was not valid to begin with.

The other problem would be having a discontiguous range that contains more
areas than the series formula has characters to define their addresses. In
other words, if I define a contiguous range, it looks like

Sheet1!$A$1:$A$10

in the series formula. If I select the ten cells individually, this becomes

(Sheet1!$A$1,Sheet1!$A$2,Sheet1!$A$3,Sheet1!$A$4,Sheet1!$A$5,Sheet1!$A$6,Sheet1!$A$7,Sheet1!$A$8,Sheet1!$A$9,Sheet1!$A$10)

At some point, this string becomes too long, and Excel chokes on the
formula.

- Jon

 

[laneer]

Jon,

Thanks for the thoughts. I checked them out - see below. Still not
working.

I'm pretty sure Excel isn't choking on a too-long formula. I've
induced this behavior with as few as 2 items in the range - so long as
they are discontinuous.

Would my approach be subject to that problem if I have many data
points? I've read elsewhere that my approach (naming a range) is a
method to avoid just that issue. However, I'm not if that method
referred to naming the range in VBA or naming it directly in Excel.
I'm using a named range in VBA.

The ranges seem valid - I inserted debug code immediately before the
line that crashes. There I can do range.select and break and the
correct ranges are selected.

Wonder if the fact that it fails in Excel 2000 fails and works in
Excel 2007 sheds some light on the behavior?

Larry Neer

 

[Jon Peltier]

The approach you've read about is using a named range in Excel. The range
variable in VBA still has to be translated into a worksheet address in
Excel, so that doesn't affect success.

Wonder if the fact that it fails in Excel 2000 fails and works in
Excel 2007 sheds some light on the behavior?

It tells me it's the opposite of most things. There are enough changes
between 2003 and 2007 that this doesn't really help nail it down.

Is this the kind of range manipulation you could do with dynamic names
instead of VBA?

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



Jon,

Thanks for the thoughts. I checked them out - see below. Still not
working.

I'm pretty sure Excel isn't choking on a too-long formula. I've
induced this behavior with as few as 2 items in the range - so long as
they are discontinuous.

Would my approach be subject to that problem if I have many data
points? I've read elsewhere that my approach (naming a range) is a
method to avoid just that issue. However, I'm not if that method
referred to naming the range in VBA or naming it directly in Excel.
I'm using a named range in VBA.

The ranges seem valid - I inserted debug code immediately before the
line that crashes. There I can do range.select and break and the
correct ranges are selected.

Wonder if the fact that it fails in Excel 2000 fails and works in
Excel 2007 sheds some light on the behavior?

Larry Neer

 

[laneer]

Doh! Tried using named ranges. Ended up with the same results. Works
fine on Vista/Office2007 and fails on W2K/Office2K. For both variants
it works fine for continuous data ranges.

 

[laneer]

As in my last post, tried various combos of Named Ranges. All of them
that I could get working in Excel 2007 broke the same way in Excel
2000.

Finally solved it by loading the X and Y coordinates into dynamic
arrays in VBA and then loading those arrays into (foo).XValues and
(foo).Values. Code snippet below

The behavior might be a bug. In any case, it's been fixed in Excel
somewhere between 2000 and 2007.

Larry Neer

===================================

Function RebuildSeries_2() As Integer
Dim tempInt As Integer, tempRange As Range
Dim myXValues() As Double, myValues() As Double
Dim i As Integer, tempCount As Integer

ReDim Preserve myXValues(nDataPoints - 1) ' array indexes start
at zero, but count from zero to (# given)
ReDim Preserve myValues(nDataPoints - 1)

tempCount = 0 ' set a counter for number of "live" data points
For i = 1 To nDataPoints
If (DataPointArray(i)) Then
myXValues(tempCount) = Sheets(activeSheetName).Cells(i +
DataAreaTop - 1, DataAreaLeft).Value
myValues(tempCount) = Sheets(activeSheetName).Cells(i +
DataAreaTop - 1, DataAreaRight).Value
tempCount = tempCount + 1
End If
Next i

ReDim Preserve myXValues(tempCount - 1) ' array indexes start at
zero, but count from zero to (# given)
ReDim Preserve myValues(tempCount - 1)
ActiveChart.SeriesCollection(2).XValues = myXValues()
ActiveChart.SeriesCollection(2).Values = myValues()

End Function