Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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 ======================== |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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,S heet1!$A$5,Sheet1!$A$6,Sheet1!$A$7,Sheet1!$A$8,She et1!$A$9,Sheet1!$A$10) At some point, this string becomes too long, and Excel chokes on the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ps.com... 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 ======================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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 On Sep 21, 6:53 pm, "Jon Peltier" wrote: 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,S heet1!$A$5,Sheet1!$A$6,Sh*eet1!$A$7,Sheet1!$A$8,Sh eet1!$A$9,Sheet1!$A$10) At some point, this string becomes too long, and Excel chokes on the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ps.com... 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 ========================- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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 _______ wrote in message oups.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 On Sep 21, 6:53 pm, "Jon Peltier" wrote: 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,S heet1!$A$5,Sheet1!$A$6,Sh*eet1!$A$7,Sheet1!$A$8,Sh eet1!$A$9,Sheet1!$A$10) At some point, this string becomes too long, and Excel chokes on the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ps.com... 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 ========================- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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. On Sep 24, 3:51 pm, "Jon Peltier" wrote: 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 _______ wrote in message oups.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 On Sep 21, 6:53 pm, "Jon Peltier" wrote: 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,S heet1!$A$5,Sheet1!$A$6,Sh**eet1!$A$7,Sheet1!$A$8,S heet1!$A$9,Sheet1!$A$10) At some point, this string becomes too long, and Excel chokes on the formula. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ups.com... 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 ========================- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XValues series breaks on disjoint ranges - but works fine in Excel 2007
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 On Sep 26, 3:48 pm, wrote: 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. On Sep 24, 3:51 pm, "Jon Peltier" wrote: 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 _______ wrote in message roups.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 On Sep 21, 6:53 pm, "Jon Peltier" wrote: 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,S heet1!$A$5,Sheet1!$A$6,Sh***eet1!$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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ wrote in message ups.com... 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 ========================- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA errors in 2007 (works fine in 2003) | Excel Discussion (Misc queries) | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
Excel VBA works fine but not in two workbooks. Help | Excel Programming | |||
Automation error -2147417848 on windows 98 while using excel in VB ( works fine in XP) | Excel Programming |