CODEWITHSUNDEEP
vbaexcel
TheCodeNovice
TheCodeNovice

Reputation: 692

Generate a multi-category axis label on al Chart

I am trying to generate multi-category chart axis in excel with VBA. (see example picture below)

My attempt at doing this with VBA, normaly i submit an array for my xvalues and this time I am trying to submit two arrays but I get type mismatch. Any ideas on how to pull this off?

With chart.SeriesCollection(1)
    .Name = "Data1"
    .Values = avgValue1
    .XValues = Array(columnVAlue, labelValue)

Pick with multiple category axis

* UPDATE *

I am using this chart from within Access so I cannot interface with a worksheet.

Upvotes: 1

Views: 1713

Answers (1)

teylyn
teylyn

Reputation: 35970

The image you post comes from a blog post by Excel charting guru Jon Peltier. In the comments of that post Jon points out a few times that a multi level axis can only be passed in from a worksheet range, not from an array.

I don’t know about charting in Access. But in Excel, you can’t pass an array as a delimited string and have the dual axis come out right. You need to use a worksheet range.

and

As I stated in my previous comment, it is not possible to pass anything other than a worksheet range that will produce a category axis with categories and subcategories. You cannot pass a string that will do this, nor can you pass a 2D array. At least I haven’t been clever enough to find a way.

So, if you use VBA to construct the chart, write the two arrays into two columns of a worksheet, then use that range for the X axis category. You can use a hidden sheet if you don't want the chart prep to upset your workbook look and feel.

Upvotes: 1

Related Questions