+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    02-07-2008
    Posts
    6

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

    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!
    Last edited by VBA Noob; 02-07-2008 at 06:40 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    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?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-07-2008
    Posts
    6

    SP1 Applied?

    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!).
    Attached Images Attached Images
    Last edited by lyndess; 02-08-2008 at 11:05 AM.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    lyndness, my apologies -- you did say SP1 in your first post, and I do not have it installed.

  5. #5
    Registered User
    Join Date
    02-07-2008
    Posts
    6

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

    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.)

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676
    If I do, I will retest and advise -- but your post was pretty persuasive.

  7. #7
    Registered User
    Join Date
    02-19-2008
    Posts
    1

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

    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)

  8. #8
    Registered User
    Join Date
    05-07-2008
    Posts
    2

    2007 SP1 XValues bug workaround

    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
    Next
    End If
    ' END OF WORKAROUND


  9. #9
    Registered User
    Join Date
    02-07-2008
    Posts
    6

    Another Workaround

    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.

  10. #10
    Registered User
    Join Date
    02-07-2008
    Posts
    6

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1