Excel 2010 VBA Chart.Axes(xlValue).MajorUnit Error RRS feed

  • Question

  • Hi, I have an Excel 2010 macro that produces 80 or so reports using a standard report template.  On the report are 2 pie charts, 4 histograms and 2 bar charts.  Some of the quantities are of the order of several hundred, but others are less than 5.  All of the quantities are integer.  I have formatted the numbers on the value axis of the histograms and bar charts not to have any decimal places.  This is fine for large quantities, but when they are small Excel sets the Major Unit to be 0.5 or less.  The values on the axis then show as something like 1, 1, 2, 2, 3 because of the rounding.  I’ve therefore included a routine that sets the MajorUnit to be 1 when Excel has decided that it should be less than this.  Most of the time this works, but not always.  It doesn’t fail consistently on the same report or the same chart within a report.  It fails with the error: -2147467259 Chart Layout Failed on the statement If .Axes(xlValue).MajorUnit < 1 Then

    Private Sub FixMajorUnits(wsReport As Worksheet)
    Dim coChartObject       As ChartObject
    For Each coChartObject In wsReport.ChartObjects
        With coChartObject.Chart
            If .HasAxis(xlValue) Then
                If  .Axes(xlValue).MajorUnit < 1 Then
                    .Axes(xlValue).MajorUnit = 1
                End If
            End If
        End With
    Next coChartObject
    End Sub

    Any suggestions on what I’ve missed?

    Thanks & regards


    Wednesday, January 23, 2013 7:42 PM