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 · Accepted Answer · 2013-10-02T22:02:18.387

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

Thanks for your quick response! I tried it but it did not work. I updated my question with related codes. Could you please look at that portion of codes and see if you find the problems. Thanks — Don, Oct 02 '13 at 18:32
One more edit, I think your problem with the commented out line is the 2. Are you intending to set it as a dual axis chart? change it to the line I have shown in my post Excel.XlAxisGroup.xlPrimary — Automate This, Oct 02 '13 at 18:59
when I comment out .SeriesCollection(2).axisgroup = 2, it should default to .SeriesCollection(2).axisgroup = Excel.XlAxisGroup.xlPrimary. I still have problem to make it according to what you suggested here. But I do see X, Y axis table shown but the scale not shown. I attached my current chart and desired chart in the edit. Do I need to manually scale it? Excel 2003 chart automatically does that for me. Why not 2010? — Don, Oct 02 '13 at 21:53
My last comment about axisgroup=2 may not be accurate for this type of chart. In my experience 2010 will automatically scale it. My next guess would be the order of operations. You can also set auto scale see edit above. — Automate This, Oct 02 '13 at 22:01

Excel 2010 Chart axis not shown up

1 Answers1