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", thenwill 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.![]()
Please Login or Register to view this content.
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:
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.![]()
Please Login or Register to view this content.
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!
Bookmarks