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