Results 1 to 4 of 4

Thread: Chart macro broken in 2007

  1. #1

    Chart macro broken in 2007



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. Below is the code and screencaptures from versions 2003 and 2010.

    Thanks,
    Robb

    [FUNCTION THAT CREATES THE CHART]
    Code:
    Private Function ChartHistogram(data As DataStruct_, ByVal StartR As Long, ByVal StartC As Long) As ChartObject
    Dim NumGroups As Integer, PlottedPointsCount As Long
    Dim R As Long, C As Long
    Dim I As Integer
    
    '########################################################
    '#                CREATE HISTOGRAM CHART                #
    '########################################################
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.Location Where:=xlLocationAsObject, Name:=SPC_RS.Name
        Set ChartHistogram = ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count)
        
    
    'ADD SERIES DATA(LINES)
        R = StartR
        C = StartC
        NumGroups = Val(txtDivisions)
        PlottedPointsCount = UBound(data.BellCurvePoints)
    
    On Error Resume Next
        For I = 1 To ActiveChart.SeriesCollection.Count
            ActiveChart.SeriesCollection(I).Delete
        Next I
    
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(1).Name = "=""Distribution"""
        
    ' Mark added the next two lines for histogram range values
        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
     
     ' Mark added these lines for display enhancement
        ActiveChart.SeriesCollection(1).Shadow = True
        'ActiveChart.SeriesCollection(1).Border.Color = vbBlue
        ActiveChart.ChartGroups(1).GapWidth = 15
        ActiveChart.Axes(xlCategory).Select
        Selection.TickLabels.Orientation = 35
        Selection.TickLabels.NumberFormat = "0.000"
        
        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
    
    'Remove Gridlines
        With ActiveChart
            .Axes(xlCategory).HasMajorGridlines = False
            .Axes(xlCategory).HasMinorGridlines = False
            .Axes(xlCategory, xlSecondary).MajorTickMark = xlNone
            .Axes(xlCategory, xlSecondary).MinorTickMark = xlNone
            .Axes(xlCategory, xlSecondary).TickLabelPosition = xlNone
            .Axes(xlValue).HasMajorGridlines = False
            .Axes(xlValue).HasMinorGridlines = False
        End With
    
    'FINAL TOUCHES
        ActiveChart.ChartArea.Interior.Color = xlAutomatic
        ActiveChart.PlotArea.Interior.Color = xlAutomatic
        ActiveChart.Legend.Interior.Color = xlAutomatic
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Characters.Text = "Histogram"
    
        ActiveChart.Axes(xlValue).CrossesAt = ActiveChart.Axes(xlValue).MinimumScale
        ActiveChart.Axes(xlCategory).CrossesAt = ActiveChart.Axes(xlCategory).MinimumScale
    
    End Function
    [SNIPPET OF THE COLLECTED DATA (there are 50 values in the Bell Curve)]
    Click image for larger version. 

Name:	ExampleData.jpg 
Views:	18 
Size:	37.2 KB 
ID:	608

    [RESULTS IN EXCEL PRIOR TO VER 2007]
    Click image for larger version. 

Name:	Example2003.jpg 
Views:	20 
Size:	22.2 KB 
ID:	606

    [RESULTS IN EXCEL 2010]
    Click image for larger version. 

Name:	Example2010.jpg 
Views:	18 
Size:	25.3 KB 
ID:	607

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Robb,

    Apologies for the late reply here.

    I'm curious, is it just that the charts manifest differently? Or are there any errors in the code?

    Is there any way that you could share some sample data in a workbook that we could run the code on?

    I am aware that many things broke between 2003 and 2007 in the charting engine. I'm happy to take a look at it if you're still looking for help.

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    Someone on another forum came up with a solution. Thank you though.
    In case someone else is searching for help with the same issue adding the following line of code fixed it.

    ActiveChart.SeriesCollection(2).AxisGroup = xlSecondary

    That line has no effect (on my macro) in Excel 2003 and makes Excel 2010 give the same output (the column series spans the width of the chart instead of aligning with the first four points of the line series)


    Robb
    Last edited by Rburn99; 2012-05-15 at 02:27 PM.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Thanks for letting me know Robb, and sorry we couldn't be a bit quicker to an answer for you here.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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