CODEWITHSUNDEEP
vbaexcelexcel-charts
anonimou
anonimou

Reputation: 49

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

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.

Upvotes: 1

Views: 7922

Answers (2)

Automate This
Automate This

Reputation: 31364

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

Upvotes: 2

hnk
hnk

Reputation: 2214

You can directly fill data into the chart using VBA without using cells

For e.g.

ActiveChart.SeriesCollection(1).XValues = "={4,5,6}"

would change the category data displayed to 4,5,6. You can generate any string you require programmatically and change it.

Upvotes: 0

Related Questions