Excel 2010 Chart axis not shown up

Question

I am converting a EXCEL 2003 application to EXCEL 2010. Data are shown up fine but the axis does not show any more. which function to show the axis with automatic scale?

For Example: If you plot the following series in an Excel Line Chart. [0.22,0.33,0.44,0.55,0.66,0.77,0.88,0.99,1.1,1.21,1.32,1.43,1.54,1.65,1.76,1.87,1.98,2.09,2.2] Excel determines that the y-axis values should be [0,0.5,1,1.5,2,2.5] [How does Excel determine the axis values for charts?1. How to make the y-axis with the automatic values [0,0.5,1,1.5,2,2.5] shown in the chart?

Thanks

Updated with related codes -

With ActiveChart
         .SeriesCollection(2).Select
         '.SeriesCollection(2).AxisGroup = 2

         .HasTitle = True
         .ChartTitle.Text = OutputTitle & Chr(10) & ChartTitle2
         .Axes(xlValue).HasTitle = True
         .Axes(xlValue).AxisTitle.Text = AxisTitle1
         .Axes(xlValue).AxisTitle.Font.Bold = False
         .HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary) = True
         .Export Filename:=ExportFile, FilterName:="GIF"
End with

If I uncomment '.SeriesCollection(2).AxisGroup = 2, I will get the y axis to show but the x axis labels are messed up with mismatch with the Values.

Current chart - enter image description here

Desired chart with scaled axis shown - enter image description here

Automate This Automate This · Accepted Answer · 2013-10-02T17:57:33

To make sure the axis is on use this:

With xlApp.ActiveChart
        .HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary) = True
End With

Range values are automatic unless otherwise specified like this:

' Set Axis Scales
    With xlApp.Charts("Chart Name").Axes(2)
        .MinimumScale = 100
        .MaximumScale = 2000
        .MajorUnit = 1000
        .MinorUnit = 100
    End With

Just to be a little more complete try explicitly addressing each value and category and see if that helps.

With xlApp.ActiveChart
  .SeriesCollection(1).XValues = "='sheet name'!R21C4:R46C4"
  .SeriesCollection(1).Values = "='sheet name'!R21C5:R46C5"
  .SeriesCollection(1).Name = "='series name'"
  .SeriesCollection(1).axisgroup = Excel.XlAxisGroup.xlPrimary
  .HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary) = True
  .HasAxis(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary) = True
  .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "x axis"
  .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "y axis"
End With

I see your axes group is set to 2, are you using dual axis?

Set it like this:

.SeriesCollection(2).axisgroup = Excel.XlAxisGroup.xlPrimary

*Edit*

To set autoscale on the axis:

.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinorUnitIsAuto = True
.Axes().MajorUnitIsAuto = True
.Axes().DisplayUnit = xlHundreds

Excel 2010 Chart axis not shown up

1 Answers