Chart macro broken as of version 2007

[Rburn99]

I have a rather large vba application written for excel to generate assorted reports on SPC data. My histogram chart broke beginning with version 2007. Same code, different results. I am unfortunately not an excel guru so I'm hoping someone here might be able to tell me why this code that works in all versions of excel prior to version 2007 no longer works. Originally I would have two series each with different amounts of data. For example series 1 has 4 values, plotted as columns and scaled to fit the width of the chart. Series 2 is a Bell Curve with 50 values plotted as a line and scaled to fit the width of the chart also. Since v2007 the 4 columns are now squished to the left side of the chart, aligned with the first 4 points of the bell curve data.

Thanks,
Robb

[PARTIAL CODE]
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:=SPC_RS.Name


'ADD SERIES DATA(LINES)
R = StartR
C = StartC
NumGroups = Val(txtDivisions)
PlottedPointsCount = UBound(data.BellCurvePoints)

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Distribution"""

ActiveChart.SeriesCollection(1).XValues = "='" & SPC_DS.Name & "'!R" & R & "C" & C & ":R" & R + NumGroups - 1 & "C" & C
C = C + 1
ActiveChart.SeriesCollection(1).Values = "='" & SPC_DS.Name & "'!R" & R & "C" & C & ":R" & R + NumGroups - 1 & "C" & C
C = C + 1

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Bell Curve"""
ActiveChart.SeriesCollection(2).Values = "='" & SPC_DS.Name & "'!R" & R & "C" & C & ":R" & R + PlottedPointsCount - 1 & "C" & C
ActiveChart.SeriesCollection(2).Border.Color = vbRed
ActiveChart.SeriesCollection(2).ChartType = xlLine

'Selection
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = False
End With

 

[RoryA]

At the end of your code, if you check the chart data, are both series plotting against the primary Y axis? Also, it appears you have 2 category axes but you don't specify any XValues for the second series - is that intentional?

 

[Rburn99]

Hello,
Sadly I can't answer that. We're vb/studio programmers and as far as getting the excel output we wanted we just searched the web at the time for how to's and examples and we recorded macros while experimenting. Now a decade later I have no idea. I don't see where you specify a particular y axis. I do see a 'primary/secondary' axis choice in Format Selection but that seems to just change the height of the bell curve some. It doesn't affect the column series not filling the width of the chart. Regarding your second question I am afraid I will have to again use the 'my limited excel experience is over a decade old' excuse. For series 1 there are two columns of data (one entered with .xvalue and the other entered with .value) The second series only has one column of data (50 points)

Example Data
http://www.brbfamily.com/ExampleData.jpg
Results prior to version 2007
http://www.brbfamily.com/Example2003.jpg
Results from version 2010
http://www.brbfamily.com/Example2010.jpg

 

[RoryA]

Can you post a sample workbook somewhere for testing? I think both your series are plotting on the primary X axis.

 

[Rburn99]

Sorry for the delay. I had to rip that portion out of the application and get it to work standalone.

Load ChartExample.xls and run ChartHistogram
http://www.brbfamily.com/ChartExample.xls

This is the results of that code from both 2003 and 2010 versions
http://www.brbfamily.com/Results.xls

 

[RoryA]

Change the code for the second series to:

    C = C + 1
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.SeriesCollection(2)
        .Name = "=""Bell Curve"""
        .Values = "=Sheet1!R2C3:R51C3"
        .Border.Color = vbRed
        .ChartType = xlLine
        .AxisGroup = xlSecondary
    End With
  'Selection
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlCategory, xlSecondary) = True
        .HasAxis(xlValue, xlPrimary) = True
        .HasAxis(xlValue, xlSecondary) = False
    End With

 

[Rburn99]

Hello Rory,

That did it! Thank you

Adding .AxisGroup = xlSecondary makes it work in 2010 and doesn't have any effect in 2003

Thanks again for taking the time to help me with this.

Robb

 

[RoryA]

my pleasure.