I am trying to generate multi-category chart axis in excel with VBA. (see example picture below)
我尝试用VBA在excel中生成多类别的图表轴。(参见下面的示例图片)
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?
我尝试用VBA来做这个，通常我为我的xvalues提交一个数组，这次我尝试提交两个数组，但是我得到了类型不匹配。有什么好办法吗?
With chart.SeriesCollection(1)
.Name = "Data1"
.Values = avgValue1
.XValues = Array(columnVAlue, labelValue)
* UPDATE *
*更新*
I am using this chart from within Access so I cannot interface with a worksheet.
我在Access中使用这个图表，因此无法与工作表交互。
1 个解决方案
#1
1
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.
你发布的图片来自Excel制图大师Jon Peltier的博客。在那篇文章的评论中Jon指出了几次，一个多级轴只能从工作表范围而不是数组中传入。
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.
我不知道如何在访问中绘制图表。但在Excel中，不能将数组作为带分隔符的字符串传递，并将双轴输出到正确的位置。您需要使用工作表范围。
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.
正如我在前面的注释中所指出的，除了工作表范围之外，不可能传递任何内容，这些工作表范围将产生具有类别和子类别的类别轴。您不能传递一个字符串来完成此操作，也不能传递一个2D数组。至少我还不够聪明，找不到办法。
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.
因此，如果使用VBA构造图表，将两个数组写入工作表的两列中，然后对X轴类别使用该范围。如果你不想让图表准备打乱你的工作簿的外观和感觉，你可以使用一个隐藏的表格。
#1
1
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.
你发布的图片来自Excel制图大师Jon Peltier的博客。在那篇文章的评论中Jon指出了几次，一个多级轴只能从工作表范围而不是数组中传入。
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.
我不知道如何在访问中绘制图表。但在Excel中，不能将数组作为带分隔符的字符串传递，并将双轴输出到正确的位置。您需要使用工作表范围。
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.
正如我在前面的注释中所指出的，除了工作表范围之外，不可能传递任何内容，这些工作表范围将产生具有类别和子类别的类别轴。您不能传递一个字符串来完成此操作，也不能传递一个2D数组。至少我还不够聪明，找不到办法。
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.
因此，如果使用VBA构造图表，将两个数组写入工作表的两列中，然后对X轴类别使用该范围。如果你不想让图表准备打乱你的工作簿的外观和感觉，你可以使用一个隐藏的表格。