PeterMo
01-22-2013, 10:21 AM
Hi, I have an Excel 2010 macro/program that produces 80 or so reports using a standard report template. On the report are 2 piecharts, 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 values 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. However, it always fails with the same error: -2147467259 Chart Layout Failed
I’ve included the routine below and highlighted the statement that fails. Any suggestions what I’ve missed?
Thanks & regards
Peter
'===================================================================
Private Sub FixMajorUnits(wsReport AsWorksheet)
Dim coChartObject As ChartObject
Dim chChart As Chart
Dim sError As String
On Error GoTo labError
For Each coChartObject In wsReport.ChartObjects
SetchChart = coChartObject.Chart
PushObj chChart
IfchChart.HasAxis(xlValue) Then
If chChart.Axes(xlValue).MajorUnit< 1 Then
chChart.Axes(xlValue).MajorUnitIsAuto = False
chChart.Axes(xlValue).MajorUnit = 1
End If
EndIf
PopObj chChart
SetchChart = Nothing
Next coChartObject
Exit Sub
labError:
sError = "Error: " & Err.Number& " " & Err.Description & " - " &chChart.Name & " - " & wsReport.Name
Debug.Print sError
Resume Next
End Sub
I’ve included the routine below and highlighted the statement that fails. Any suggestions what I’ve missed?
Thanks & regards
Peter
'===================================================================
Private Sub FixMajorUnits(wsReport AsWorksheet)
Dim coChartObject As ChartObject
Dim chChart As Chart
Dim sError As String
On Error GoTo labError
For Each coChartObject In wsReport.ChartObjects
SetchChart = coChartObject.Chart
PushObj chChart
IfchChart.HasAxis(xlValue) Then
If chChart.Axes(xlValue).MajorUnit< 1 Then
chChart.Axes(xlValue).MajorUnitIsAuto = False
chChart.Axes(xlValue).MajorUnit = 1
End If
EndIf
PopObj chChart
SetchChart = Nothing
Next coChartObject
Exit Sub
labError:
sError = "Error: " & Err.Number& " " & Err.Description & " - " &chChart.Name & " - " & wsReport.Name
Debug.Print sError
Resume Next
End Sub