Category name in excel charts, using VBA without using any Cells

vba excel excel-charts
anonimou picture anonimou · Jul 3, 2014 · Viewed 7k times · Source

I'm wanting to do a chart where the ticks on the X axis are not numbers but texts. For the purpose of learning: monthes like "January, August, February, November" or "Apples, Bananas, Cars".

The trick is that I don't want these informations coming from any cells. It will be coded on Excel VBA.

Maybe it is very very easy. I just don't know HOW look for it. I've beeing trying for the last couple hours, but still nothing. Look that it isn't the label of the axis that I'm wanting to change, but every data pointed in the Y-axis will have it name.

Thanks in advance.

Answer

Automate This picture Automate This · Jul 3, 2014

Starting with data that looks like this:

enter image description here

You can set the axis labels using this line:

ActiveChart.SeriesCollection(1).XValues = "={""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun""}"

Full Example code:

Sub AddChart()
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$A$1:$A$6"
    ActiveChart.SeriesCollection(1).XValues = "={""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun""}"
End Sub

Results:

enter image description here

Related questions