Excel 2007 : Excel 2007 SP1 Bug: Series.XValues(n) Returns Incorrect Values on Scatter Plot with T

**lyndess** · 02-07-2008, 06:32 PM

Cross post here

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then

Please Login or Register  to view this content.

will incorrectly return "5", rather than the x-value of the fifth point in the series. This seems to occur only when there are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:
* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...
* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:

Please Login or Register  to view this content.

The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, but—as I indicated above—I'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

**shg** · 02-07-2008, 07:34 PM

It's thoughtful of you to post.

I implemented your example in Excel 2003 and it worked fine.
I opened the example in Excel 2007 in Compatibility mode, also fine.
I saved the file as an xlsm, closed and re-opened in native mode, and it worked fine.
I created a new (native 2007) workbook, ran the same procedure end to end, and it worked fine (attached).

In each case, I check both series. Bottom line, I can't duplicate your result. Your thoughts?

**lyndess** · 02-08-2008, 10:55 AM

Thanks for your respose, shg!

Hmmm... The only thing I can think of is to ask—have you applied Office 2007 service pack 1 (SP1), which just came out recently? ("Orb" > Excel Options > Resources > About)

I duplicated this error on two different machines (both running Windows Vista). Everything worked fine before applying SP1, and the problem arose in both cases after applying SP1.

Here are two screenshots that show (1) the worksheet you attached to your message, and (2) the output in theImmediate Window of the Visual Basic Editor. You can see that—in my case—the XValues in the VBE differ from those on the worksheet (although it's interesting to note that it retains the number formatting!).

**shg** · 02-08-2008, 02:19 PM

lyndness, my apologies -- you did say SP1 in your first post, and I do not have it installed.

**lyndess** · 02-08-2008, 03:37 PM

So you are going to install SP1 and test this again?

I'm sure that this is the ONLY thing that SP1 breaks.

(Just kidding, of course.)

**shg** · 02-08-2008, 05:11 PM

If I do, I will retest and advise -- but your post was pretty persuasive.

**JotM** · 02-19-2008, 06:58 AM

Hello,

I can validate this bug! Before installing SP1 my macro worked quite well.
I have the same proplem with SP1.
I can't evaluate the x-Values of my xy-chart-data to determine the range of the x-axis:

code
...
'Ermittlung der auftretenden Maxima und Minima
xMin0 = 1000000000
xMax0 = -1000000000
For I = 1 To ActiveChart.SeriesCollection.Count
xMin0 = Application.WorksheetFunction.Min(ActiveChart.SeriesCollection(I).XValues, xMin0)
On Error Resume Next
xMax0 = Application.WorksheetFunction.Max(ActiveChart.SeriesCollection(I).XValues, xMax0)
On Error Resume Next
Next I
s2 = "x: [" & Round(xMin0, 2) & "..." & Round(xMax0, 2) & "]"
...

xMin0 and xMax0 stay unchanged

The same macro works correct in excel 2000 or excel 2007 without SP1 (compare attachment)

**Phil Outram** · 05-07-2008, 10:25 PM

Hi,

I've also hit this problem, which completely broke my graph zooming tool. I've had to add a slightly crude work-around until the problem is fixed. This just uses the trace's formula and raw data. Feel free to use, or improve upon.

Cheers,
Phil.

varSampleDataX = ActiveChart.SeriesCollection(i).XValues
varSampleDataY = ActiveChart.SeriesCollection(i).Values

' WORKAROUND for very annoying SP1 bug in 2007
If (varSampleDataX(lNewDataCount) = lNewDataCount) Then

' The ActiveChart.SeriesCollection(i).XValues array just contains the index number.
' We therefore need to get the data from the formula for the X-values.
Dim strformula As String
strformula = ActiveChart.SeriesCollection(i).formula
strformula = Right(strformula, Len(strformula) - InStr(strformula, "("))
If (Left(strformula, 1) = Chr(34)) Then

' The trace name is specified in quotes, so may contain a comma, so get to the next
' quotes before searching for a comma to find the X values formula string.
strformula = Right(strformula, Len(strformula) - 1)
strformula = Right(strformula, Len(strformula) - InStr(strformula, Chr(34)))

End If
' Get the formula for the XValues
strformula = Right(strformula, Len(strformula) - InStr(strformula, ","))
strformula = Left(strformula, InStr(strformula, ",") - 1)
Dim index As Integer
index = 1
Dim c As range
For Each c In range(strformula)

varSampleDataX(index) = c.Value
index = index + 1

**lyndess** · 06-17-2008, 09:50 AM

Inspired by Phil's workaround, I finally got around to writing my own workaround that can handle situations where the source of the x-values is a contiguous range, a named range, a discontiguous range (i.e., multiple areas), or even an array of values (i.e., no cell references). My code also handles sitations where the first parameter of the SERIES function (the series name) contains commas or quotation marks in the name.

I hope this proves helpful! Please reply with corrections and/or suggested improvements.

(NOTE: This code has only been tested on Excel 2007 with SP1.)

Please Login or Register  to view this content.

If you want to test the function, create a scatter plot (as described previously in this thread) and then run this procedure:

Please Login or Register  to view this content.

**lyndess** · 06-06-2009, 12:09 PM

PS: This bug seems to have been fixed with Excel 2007 SP2!

Excel 2007 : Excel 2007 SP1 Bug: Series.XValues(n) Returns Incorrect Values on Scatter Plot with T

LinkBack

Thread Tools

Rate This Thread

Display

Excel 2007 SP1 Bug: Series.XValues(n) Returns Incorrect Values on Scatter Plot with T

SP1 Applied?

Excel 2007 SP1 Bug: Series.XValues(n) Returns Incorrect Values on Scatter Plot

Excell 2007 SP1 Bug Series.Collection(i).XValues returns incorrect values

2007 SP1 XValues bug workaround

Another Workaround

Re: Excel 2007 SP1 Bug: Series.XValues(n) Returns Incorrect Values on Scatter Plot wi

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions