Reputation: 1572
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 -
Desired chart with scaled axis shown -
Upvotes: 0
Views: 2208
Reputation: 31364
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
Upvotes: 2